How do you delete duplicate rows but keep one? | Interview Question

Q: How do you delete duplicate rows but keep one?

**Target Audience:** Developers who can find duplicates and now need to remove them without losing data, and candidates preparing for the classic "delete duplicates, keep one" question. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server forms are cited, not run. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A newsletter has 10 sign-ups but only 6 real people: Asha signed up three times, Ben twice, and Chen once in lowercase and once in capitals. Every send goes out three times to Asha, and she is about to hit "report spam". Deleting all the Asha rows would lose her. Del

The problem

Question presented to candidate: "We found duplicate subscribers: the same email signed up two or three times. Write the SQL to delete the duplicates but keep exactly one row per email. How do you choose which one survives, and how would you do it safely on a big production table?"

What a strong answer should cover:

Decide the survivor rule first (oldest id, newest row, most complete row) and make it deterministic with a tie-breaker
Pattern 1: DELETE ... WHERE id NOT IN (SELECT MIN(id) ... GROUP BY key); pattern 2: ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) and delete rn > 1
NULL keys: GROUP BY lumps all NULLs together, so a careless query deletes rows that are not duplicates
Tables without a key: SQLite rowid, PostgreSQL ctid, or copy the distinct rows into a new table
Engine detail: PostgreSQL DELETE ... USING and RETURNING; MySQL rejects a subquery on the target table (error 1093) unless it is wrapped in a derived table
Safety: preview with a SELECT first, run in a transaction, batch large deletes, then add a UNIQUE constraint

Clarifying questions expected:

Which copy should we keep: the first sign-up, the latest, or the one with the most data?
Do other tables reference these rows through foreign keys (do child rows need re-pointing first)?
How big is the table, and can it be locked or must the delete run while traffic continues?

Code / implementation expected: Yes — a DELETE that keeps one row per group, with the survivor chosen by MIN(id) or ROW_NUMBER().

Schema used by the examples:

duplicates
delete

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who can find duplicates and now need to remove them without losing data, and candidates preparing for the classic "delete duplicates, keep one" question. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server forms are cited, not run.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A newsletter has 10 sign-ups but only 6 real people: Asha signed up three times, Ben twice, and Chen once in lowercase and once in capitals. Every send goes out three t

The solution is hidden so you can try first

14 min read · 13 sections

Run the code

Edit the query and run it against the tables for this question.

SQLKeep the oldest row per email (lowest id) with NOT IN and MIN(id)

SQLKeep the newest row per email with ROW_NUMBER() and delete rn > 1

SQLA table with no key: tell identical rows apart by SQLite rowid, then add a UNIQUE index

PostgreSQL: DELETE ... USING a ranked CTE, with RETURNING to see what was removed

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY LOWER(email) ORDER BY id) AS rn
  FROM subscribers
  WHERE email IS NOT NULL
),
deleted AS (
  DELETE FROM subscribers AS s
  USING ranked AS r
  WHERE s.id = r.id AND r.rn > 1
  RETURNING s.id, s.email, s.name
)
SELECT id, email, name FROM deleted ORDER BY id;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.