How do you deduplicate a table whose rows are byte-identical and that has no unique column?

seniorshould knowfreq 28%

answer

numbering the rows is not the hard part
a predicate cannot tell the copies apart
any WHERE that hits one hits both
think about replacing the table, not editing it

basics

~20 s

ROW_NUMBER can still number identical rows, but no portable DELETE can target one copy, because any predicate matching a loser matches the survivor too. Rebuild the table from the rn = 1 set and swap it in.

solid answer

~50 s

The numbering is not the problem — ROW_NUMBER() OVER (PARTITION BY every_column ORDER BY every_column) happily assigns 1, 2, 3 to identical rows. The problem is row identity: a DELETE removes every row its WHERE matches, and identical rows are indistinguishable to any predicate, so you would delete the whole group. The portable answer is to rebuild rather than delete in place:

sql

CREATE TABLE events_dedup AS
SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY source, payload, occurred_at
                                 ORDER BY source) AS rn
  FROM events e) r
WHERE rn = 1;

Then swap the tables in a transaction and add the UNIQUE constraint that should have existed. A variant that avoids a full copy is to stage the distinct rows of only the affected groups, delete those groups outright, and re-insert. Some engines expose a physical row locator that makes an in-place delete possible, but that is a dialect feature, not portable SQL.

The numbering is not the problem — `ROW_NUMBER() OVER (PARTITION BY every_column ORDER BY every_column)` happily assigns 1, 2, 3 to identical rows. The problem is **row identity**: a `DELETE` removes every row its `WHERE` matches, and identical rows are indistinguishable to any predicate, so you would delete the whole group. The portable answer is to rebuild rather than delete in place: ```sql CREATE TABLE events_dedup AS SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (PARTITION BY source, payload, occurred_at ORDER BY source) AS rn FROM events e) r WHERE rn = 1; ``` Then swap the tables in a transaction and add the `UNIQUE` constraint that should have existed. A variant that avoids a full copy is to stage the distinct rows of only the affected groups, delete those groups outright, and re-insert. Some engines expose a physical row locator that makes an in-place delete possible, but that is a dialect feature, not portable SQL.

code

sql · 11 lines

sql

CREATE TABLE events_dedup AS
SELECT source, payload, occurred_at
FROM (
    SELECT e.*,
           ROW_NUMBER() OVER (PARTITION BY source, payload, occurred_at
                              ORDER BY source) AS rn
    FROM events e
) r
WHERE rn = 1;

-- verify, then swap the tables and recreate indexes and constraints

go deeper

for a junior

Know that removing one of two completely identical rows is not something a plain DELETE can do, because its WHERE matches both, and that the usual fix is to rebuild the table from a deduplicated SELECT.

for a middle

Explain row identity as the missing ingredient: the ranked CTE still numbers the copies, but the outer statement needs a unique column to aim at, which is exactly what the table lacks.

for a senior

Compare the repairs on cost and risk — full rebuild and swap versus deleting whole duplicate groups and re-inserting one copy — and handle indexes, grants, concurrent writers and the transaction boundary.

for a principal

Treat it as a schema failure: a table with no key will keep producing this incident, so the deliverable is the key, the idempotent load path and a migration plan for the swap, not a clever one-off statement.

Why this case is different

The usual deduplication story assumes the duplicates differ somewhere — different id, different created_at — so the ranked CTE can hand the outer DELETE a primary key to aim at. Strip that away and the pattern breaks at the last step. Two rows identical in every column cannot be told apart by any expression over their columns, and a DELETE has nothing else to work with: DELETE FROM events WHERE source = 'x' AND payload = 'y' matches the copy you wanted to keep just as well as the one you wanted gone. This shows up in append-only import tables, staging tables loaded twice, and log tables created without a key.

Ranking still works

Window functions have no trouble with identical rows. Partitioning by the full column list puts each set of identical rows in one partition, and ROW_NUMBER numbers them 1..n even though the ordering is entirely arbitrary between them — arbitrary is fine here, because the rows are interchangeable. So the survivor set is easy to compute; what is missing is a way to address the losers.

sql

SELECT source, payload, occurred_at
FROM (SELECT e.*,
             ROW_NUMBER() OVER (PARTITION BY source, payload, occurred_at
                                ORDER BY source) AS rn
      FROM events e) r
WHERE rn = 1;

Since the rows are identical in every column, SELECT DISTINCT * computes the same set; the ranked form matters when only some columns define duplication and you must keep whole rows.

Rebuild and swap

The portable repair replaces the table instead of editing it:

Create a new table from the deduplicated SELECT (CREATE TABLE ... AS SELECT; the exact spelling of create-from-query varies by engine).
Verify the row counts: the new count should equal the number of distinct groups.
Rename the old table aside and the new one into its place, inside a transaction if the engine allows transactional DDL, or during a maintenance window if not.
Recreate indexes, constraints and grants on the new table — a create-from-query copies data and column types, not the schema objects around them.
Add the UNIQUE constraint on the real business key so the situation cannot recur.

The cost is a full copy of the table plus a period where writes must be paused or replayed. For a large hot table that may be unacceptable, which pushes you to the second option.

Delete-the-group and re-insert

If only a small fraction of the table is affected, you can repair in place without row identity:

sql

CREATE TEMPORARY TABLE keepers AS
SELECT DISTINCT source, payload, occurred_at
FROM events e
WHERE EXISTS (SELECT 1 FROM events d
              WHERE d.source = e.source AND d.payload = e.payload
                AND d.occurred_at = e.occurred_at
              GROUP BY d.source, d.payload, d.occurred_at
              HAVING COUNT(*) > 1);

DELETE FROM events
WHERE (source, payload, occurred_at) IN (SELECT source, payload, occurred_at FROM keepers);

INSERT INTO events (source, payload, occurred_at)
SELECT source, payload, occurred_at FROM keepers;

The trick is that deleting all copies of a duplicated group is something a predicate can express; you then put one copy back. It must run in a single transaction, and concurrent writers to those groups have to be considered, but it touches only the duplicated rows rather than the whole table.

The dialect escape hatch

Several engines expose a physical locator for a stored row, and where one exists it restores row identity and lets an ordinary ranked delete work in place. That is a vendor feature with vendor-specific semantics and lifetime rules, so treat it as an optimisation available on the engine you happen to be on, not as the answer to a portable-SQL question.

What the question is really testing

It separates candidates who have memorised the rn > 1 recipe from those who understand why it works. The recipe depends on a unique column, and the interviewer removed it deliberately. The strong answer names row identity as the missing ingredient, offers rebuild-and-swap as the portable repair, mentions the delete-group-and-reinsert variant for a large table with few duplicates, and closes by adding the constraint that makes the whole exercise a one-off.

## Why this case is different The usual deduplication story assumes the duplicates differ somewhere — different `id`, different `created_at` — so the ranked CTE can hand the outer `DELETE` a primary key to aim at. Strip that away and the pattern breaks at the last step. Two rows identical in every column cannot be told apart by any expression over their columns, and a `DELETE` has nothing else to work with: `DELETE FROM events WHERE source = 'x' AND payload = 'y'` matches the copy you wanted to keep just as well as the one you wanted gone. This shows up in append-only import tables, staging tables loaded twice, and log tables created without a key. ## Ranking still works Window functions have no trouble with identical rows. Partitioning by the full column list puts each set of identical rows in one partition, and `ROW_NUMBER` numbers them 1..n even though the ordering is entirely arbitrary between them — arbitrary is fine here, because the rows are interchangeable. So the *survivor set* is easy to compute; what is missing is a way to address the losers. ```sql SELECT source, payload, occurred_at FROM (SELECT e.*, ROW_NUMBER() OVER (PARTITION BY source, payload, occurred_at ORDER BY source) AS rn FROM events e) r WHERE rn = 1; ``` Since the rows are identical in every column, `SELECT DISTINCT *` computes the same set; the ranked form matters when only *some* columns define duplication and you must keep whole rows. ## Rebuild and swap The portable repair replaces the table instead of editing it: 1. Create a new table from the deduplicated `SELECT` (`CREATE TABLE ... AS SELECT`; the exact spelling of create-from-query varies by engine). 2. Verify the row counts: the new count should equal the number of distinct groups. 3. Rename the old table aside and the new one into its place, inside a transaction if the engine allows transactional DDL, or during a maintenance window if not. 4. Recreate indexes, constraints and grants on the new table — a create-from-query copies data and column types, not the schema objects around them. 5. Add the `UNIQUE` constraint on the real business key so the situation cannot recur. The cost is a full copy of the table plus a period where writes must be paused or replayed. For a large hot table that may be unacceptable, which pushes you to the second option. ## Delete-the-group and re-insert If only a small fraction of the table is affected, you can repair in place without row identity: ```sql CREATE TEMPORARY TABLE keepers AS SELECT DISTINCT source, payload, occurred_at FROM events e WHERE EXISTS (SELECT 1 FROM events d WHERE d.source = e.source AND d.payload = e.payload AND d.occurred_at = e.occurred_at GROUP BY d.source, d.payload, d.occurred_at HAVING COUNT(*) > 1); DELETE FROM events WHERE (source, payload, occurred_at) IN (SELECT source, payload, occurred_at FROM keepers); INSERT INTO events (source, payload, occurred_at) SELECT source, payload, occurred_at FROM keepers; ``` The trick is that deleting *all* copies of a duplicated group is something a predicate **can** express; you then put one copy back. It must run in a single transaction, and concurrent writers to those groups have to be considered, but it touches only the duplicated rows rather than the whole table. ## The dialect escape hatch Several engines expose a physical locator for a stored row, and where one exists it restores row identity and lets an ordinary ranked delete work in place. That is a vendor feature with vendor-specific semantics and lifetime rules, so treat it as an optimisation available on the engine you happen to be on, not as the answer to a portable-SQL question. ## What the question is really testing It separates candidates who have memorised the `rn > 1` recipe from those who understand *why* it works. The recipe depends on a unique column, and the interviewer removed it deliberately. The strong answer names row identity as the missing ingredient, offers rebuild-and-swap as the portable repair, mentions the delete-group-and-reinsert variant for a large table with few duplicates, and closes by adding the constraint that makes the whole exercise a one-off.

Why can't the ranked-CTE delete work here at all?
Because the outer DELETE has to name the losers with a predicate, and every predicate over the columns is equally true of the survivor. The ranked set knows which copy is number 2, but there is no expression that carries that knowledge back to a specific stored row without a unique column.
When is deleting whole duplicate groups and re-inserting one copy better than a full rebuild?
When the table is large and only a small share of rows are duplicated. A rebuild copies everything and needs a swap window; the delete-and-reinsert touches only the affected groups. It must run in one transaction, and you have to account for concurrent writers to those same groups.
After the cleanup, what prevents the duplicates from returning?
A UNIQUE constraint on the columns that define a duplicate, so a second identical insert fails instead of accumulating. If the load path legitimately retries, make it idempotent — dedupe in the loading query, or key the insert so a repeat is a no-op rather than a new row.
Does SELECT DISTINCT solve the problem?
It computes the deduplicated set but changes nothing on disk — it is a query, not a repair. It is useful as the source of the rebuild, and useless as a strategy of adding DISTINCT to every reader, which hides the duplicates while they keep growing and keeps every aggregate on the table wrong.

saying these in an interview costs you the question

Proposes DELETE with rn > 1 even though no unique column exists
Says SELECT DISTINCT removes the duplicates from the table
Adds DISTINCT to every query instead of repairing the data
Assumes a physical row locator exists in every engine
Forgets to recreate indexes and constraints after a rebuild

How do you deduplicate a table whose rows are byte-identical and that has no unique column?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Why this case is different

Ranking still works

Rebuild and swap

Delete-the-group and re-insert

The dialect escape hatch

What the question is really testing

▾common follow-ups4 questions

▾red flags5 flags

§ context

▾related questions4 questions

▾related topics4 topics

Why this case is different

Ranking still works

Rebuild and swap

Delete-the-group and re-insert

The dialect escape hatch

What the question is really testing

answer

go deeper

context