# Separate unique rows from duplicates

Shape / rows

A variant of [Deduplicate rows](https://meridian.online/operations/deduplicate-rows).

Send the first row per key to one output and the repeats to a second output.

Page: <https://meridian.online/operations/deduplicate-rows/separate-unique-rows-from-duplicates>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

send the first row of each key to one output and its repeats to another

```sql
CREATE TEMP TABLE signups AS SELECT * FROM (VALUES
  (1, 'ada@example.com', 'Ada',    DATE '2026-01-03'),
  (2, 'bo@example.com',  'Bo',     DATE '2026-01-04'),
  (3, 'ada@example.com', 'Ada L.', DATE '2026-01-09'),
  (4, 'cy@example.com',  'Cy',     DATE '2026-01-10'),
  (5, 'bo@example.com',  'Bo',     DATE '2026-01-04'),
  (6, 'ada@example.com', 'Ada',    DATE '2026-01-03'),
  (7, 'di@example.com',  'Di',     DATE '2026-01-15')
) t(line, email, name, signed_up);

CREATE TEMP TABLE numbered AS
SELECT *, row_number() OVER (PARTITION BY email ORDER BY line) AS occurrence FROM signups;
CREATE TEMP TABLE unique_rows AS SELECT line, email, name, signed_up FROM numbered WHERE occurrence = 1;
CREATE TEMP TABLE repeat_rows AS SELECT line, email, name, signed_up FROM numbered WHERE occurrence > 1;
SELECT * FROM unique_rows ORDER BY line;
SELECT * FROM repeat_rows ORDER BY line;
SELECT CASE WHEN ((SELECT list(line ORDER BY line) FROM unique_rows) = [1, 2, 4, 7] AND (SELECT list(line ORDER BY line) FROM repeat_rows) = [3, 5, 6] AND (SELECT count(*) FROM unique_rows) + (SELECT count(*) FROM repeat_rows) = (SELECT count(*) FROM signups) AND (SELECT count(*) FROM unique_rows u JOIN repeat_rows r USING (line)) = 0 AND (SELECT count(DISTINCT email) FROM unique_rows) = 4) THEN 'as expected' ELSE error('the first rows were not lines 1, 2, 4 and 7, or the repeats were not lines 3, 5 and 6, or the two outputs overlap or miss a row') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Alteryx Designer: [`Unique`](https://help.alteryx.com/current/en/designer/tools/preparation/unique-tool.html) (read 2026-09-27)
- Apache NiFi: [`DetectDuplicate`](https://nifi.apache.org/components/org.apache.nifi.processors.standard.DetectDuplicate/) (read 2026-09-27)
- Talend: [`tUniqRow`](https://help.qlik.com/talend/en-US/components/8.0/deduplication/deduplication-component) (read 2026-09-27)