# Keep only the duplicates

Shape / rows

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

Keep only the rows whose whole row or key appears more than once.

Page: <https://meridian.online/operations/deduplicate-rows/keep-only-duplicates>

## Status

Runs as a SQL step.

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

## The DuckDB step

keep only the rows whose key or whole row appears more than once

```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);

-- every row of an email that appears more than once, the first occurrence included
CREATE TEMP TABLE dup_emails AS
SELECT * FROM signups QUALIFY count(*) OVER (PARTITION BY email) > 1;
-- every row that appears more than once as a whole row (line left out)
CREATE TEMP TABLE dup_rows AS
SELECT * FROM signups QUALIFY count(*) OVER (PARTITION BY email, name, signed_up) > 1;
SELECT * FROM dup_emails ORDER BY line;
SELECT * FROM dup_rows ORDER BY line;
SELECT CASE WHEN ((SELECT list(line ORDER BY line) FROM dup_emails) = [1, 2, 3, 5, 6] AND (SELECT list(line ORDER BY line) FROM dup_rows) = [1, 2, 5, 6]) THEN 'as expected' ELSE error('the repeated emails were not lines 1, 2, 3, 5 and 6, or the repeated whole rows were not lines 1, 2, 5 and 6') 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).

- EasyMorph: [`Keep duplicates`](https://help.easymorph.com/doku.php?id=transformations:keepduplicates) (read 2026-09-27)