Shape / rows · All operations

Deduplicate rows

Distinct rows, unique by key, keep first or last per key.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
fewer rows, same columns
Level
several clauses of SQL, or a window

The DuckDB step

Keep the latest order per product and amount

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT DISTINCT ON (product, amount) * FROM orders ORDER BY product, amount, order_date DESC;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 9 AND count(*) FILTER (WHERE order_id = 8) = 1 AND count(*) FILTER (WHERE order_id = 6) = 0 FROM result)) THEN 'as expected' ELSE error('the duplicate widget order was not reduced to the later one') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the duplicate widget order was not reduced to the later one”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Keep one row per key

Drop repeated rows so one row survives for each distinct whole row or key, chosen by order where it matters.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (1)
  • Uniqueread 2026-09-27

    Split records into unique and duplicate outputs by grouping on chosen fields

Apache DataFusion (4)
  • DISTINCTread 2026-09-27

    Remove duplicate rows from the result

  • DISTINCT ONread 2026-09-27

    Keep one row per distinct value of listed expressions, chosen by ORDER BY

  • distinctread 2026-09-27

    DataFrame method: Remove duplicated rows

  • distinct_onread 2026-09-27

    DataFrame method: Keep one row per value of listed expressions, by sort order

Apache Hop (2)
  • Unique Rowsread 2026-09-27

    Remove consecutive duplicate rows from sorted input, optionally counting them

  • Unique Rows (HashSet)read 2026-09-27

    Remove duplicate rows from unsorted input with an in-memory hash set

Apache NiFi (2)
  • DeduplicateRecordread 2026-09-27

    Drop duplicate records within a record set, or across FlowFiles with a distributed map cache

  • DetectDuplicateread 2026-09-27

    Route a FlowFile as duplicate when a value computed from its attributes is already cached

AWS Glue (4)
  • Drop Duplicatesread 2026-09-27

    Remove duplicate rows, matching on entire rows or on chosen keys

  • FlagDuplicateRowsread 2026-09-27

    Add a column that flags rows that exactly match an earlier row

  • FlagDuplicatesInColumnread 2026-09-27

    Add a column that flags rows whose value in a source column appeared in an earlier row

  • RemoveDuplicatesread 2026-09-27

    Delete a row when its value in a chosen column duplicates an earlier one

Bruin (1)
dbt-utils (1)
  • deduplicateread 2026-09-27

    Keep one row per partition, chosen by an order-by priority

Duckle (1)
  • Distinctread 2026-09-25

    drop duplicate rows, or DISTINCT ON key columns with a tiebreak order

EasyMorph (2)
  • Deduplicate rowsread 2026-09-27

    Remove duplicate rows judged on all or selected columns

  • Keep duplicatesread 2026-09-27

    Keep the rows that are duplicated on all or selected columns

Ibis (1)
  • Distinctread 2026-09-27

    Keep the distinct rows of a table

KNIME Analytics Platform (1)
  • Duplicate Row Filterread 2026-09-27

    Remove or flag rows that repeat the same values in chosen columns

OpenRefine (1)
Pentaho Data Integration (2)
  • Unique Rowsread 2026-09-27

    Remove consecutive duplicate rows from sorted input

  • Unique Rows (HashSet)read 2026-09-27

    Remove duplicate rows with a hash set, without needing sorted input

Polars (4)
Power Query M (1)
  • Table.Distinctread 2026-09-27

    Remove duplicate rows, comparing whole rows or named columns

RapidMiner Studio (2)
SQLMesh (1)
  • @DEDUPLICATEread 2026-09-27

    Keep one row per partition by a window function and an order-by

Tableau Prep (1)
  • Identify Duplicate Rowsread 2026-09-27

    Add an Is Duplicate Row? field over all or selected fields, then keep the unique rows

Talend (1)
  • tUniqRowread 2026-09-27

    Compare rows on key columns and separate unique rows from duplicates

Trifacta (2)
VisiData (1)
  • dedupe-rowsread 2026-09-23

    Open sheet keeping the first row per key, or per all visible columns when no key

Background

In PostgreSQL, SELECT DISTINCT removes all duplicate rows from the result set, and one row is kept from each group of duplicates.[1]

When to use it

In PostgreSQL, the first row of each DISTINCT ON group is unpredictable unless ORDER BY is used to ensure that the desired row appears first.[1] In DuckDB, when DISTINCT ON has no ORDER BY clause, the row returned for each value is not defined and can be any row in the table.[2] In Polars, DataFrame.unique keeps any one of the duplicate rows by default, with no guarantee of which row is kept.[3] Whenever it matters which row survives, name the ordering that picks it, and break ties on a column that is unique, so the same input always keeps the same row. (Meridian's view)

In Excel, a comparison of duplicate values depends on what appears in the cell, not the underlying value, so one date in two formats counts as two unique values.[4] In Excel, removing duplicate values deletes them permanently, while filtering for unique values only hides the duplicates temporarily.[4] In SQL, the DISTINCT keyword treats two NULLs as not distinct, although the standard defines all NULL markers as unequal to one another.[5] Standardise text, case and formats before deduplicating, so that rows meant to be the same are written the same way. (Meridian's view)

Record linkage, the task of finding records in a data set that refer to the same entity, also goes by names that include duplicate detection and deduplication.[6] Exact deduplication suits copies that are identical, such as a file loaded twice; when one customer is spelled two ways, the job is record linkage instead. (Meridian's view)

The method

In SQL, a plain projection returns a multiset rather than a set, and the DISTINCT keyword gives the relational-algebra projection by eliminating duplicate data.[7] In PostgreSQL, SELECT ALL is the default and returns all candidate rows, including duplicates.[1] In PostgreSQL, the UNION, INTERSECT and EXCEPT operators eliminate duplicate rows unless ALL is specified.[1]

PostgreSQL's SELECT DISTINCT ON keeps only the first row of each set of rows where the given expressions evaluate to equal.[1] In PostgreSQL, DISTINCT ON is an extension of the SQL standard.[1] In PostgreSQL, the DISTINCT ON expressions must match the leftmost ORDER BY expressions, and the ORDER BY normally carries further expressions that set the precedence of rows within each group.[1] In DuckDB, the row_number window function gives the number of the current row within its partition, counting from 1.[8] In DuckDB, the QUALIFY clause filters the results of window functions, much as HAVING filters the results of aggregate functions, without the need for a subquery.[9]

Apache Hop's Unique Rows transform removes duplicate rows from a sorted input stream.[10] Apache Hop's Unique Rows (HashSet) transform keeps track of the duplicate rows in memory and does not require a sorted input.[11] In Apache Hop, the Unique Rows (HashSet) transform risks hash collisions on larger sets of data unless it compares the stored row values.[11] In DuckDB, queries starting with SELECT DISTINCT run deduplication, which is an expensive operation.[2]

Compared with the alternatives

In SQL, GROUP BY is often used with aggregate functions or to eliminate duplicate rows from a result set.[12] Use GROUP BY when the repeated rows should be summarised, with a count or a latest date, and DISTINCT when one copy is all that is wanted. (Meridian's view) In PostgreSQL, UNION ALL is usually significantly quicker than UNION, because ALL prevents the elimination of duplicates.[1]

Sources

  1. [1]PostgreSQL: Documentation: 18: SELECTdocumentation
  2. [2]SELECT Clause – DuckDBdocumentation
  3. [3]polars.DataFrame.unique — Polars documentationdocumentation
  4. [4]Filter for unique values or remove duplicate values | Microsoft Supportdocumentation
  5. [5]Null (SQL) - Wikipediasecondary source
  6. [6]Record linkage - Wikipediasecondary source
  7. [7]Projection (relational algebra) - Wikipediasecondary source
  8. [8]Window Functions – DuckDBdocumentation
  9. [9]QUALIFY Clause – DuckDBdocumentation
  10. [10]Unique Rows | Apache Hopdocumentation
  11. [11]Unique Rows (HashSet) | Apache Hopdocumentation
  12. [12]Select (SQL) - Wikipediasecondary source

Used in

One Protocol uses this operation: