Remove Duplicate Records

Hook: Interviewers love this question because deduping looks simple until you must choose which copy survives.

Question: How do I remove duplicate records from a SQL table?

Answer: First define what makes two rows duplicates, like the same email or the same customer_id plus date of birth. Then use a window function such as ROW_NUMBER() to rank rows inside each duplicate group, keep the row with number 1, and delete the rest. If the table does not have a stable unique key, add one or use a database-specific row id so you can delete the exact physical row safely.

Interview-Ready Answer: I’d start by defining the business key that makes a row duplicate, such as email or a composite key. Then I’d assign ROW_NUMBER() over that key, order the rows by the version I want to keep, usually the oldest or newest, and delete every row with rn > 1. I’d do it in a transaction, because cleanup work should be repeatable and safe, and if the table has no primary key I’d add a surrogate key or use a database-specific row identifier to target rows precisely.

What counts as a duplicate?

SQL does not know your business rule. Two rows are duplicates only if your system says they are—for example same email, or same first_name + last_name + dob. The first job is always to choose the duplicate key and the tie-breaker, which is the column used to decide which row survives.

How the delete works under the hood

1. Group rows by the duplicate key with PARTITION BY. A partition is just a bucket of rows that share the same key.
2. Order rows inside each bucket by the rule you trust, such as oldest created_at first or highest id first. This step must be deterministic, meaning the same input always picks the same winner.
3. Assign ROW_NUMBER() starting at 1 for the winner, then 2, 3, and so on for the extras.
4. Delete every row whose row number is greater than 1. This keeps exactly one row per duplicate group.
5. Verify the result with a GROUP BY ... HAVING COUNT(*) > 1 check, and then add a unique constraint or unique index if the business rule should never be violated again.

Why this approach is popular

SELECT DISTINCT is great for reading unique results, but it does not change stored data. The ROW_NUMBER() pattern is better for cleanup because it lets you say exactly which copy stays.

Performance and edge cases

The window query usually needs to sort rows by the partition and order columns, so think about O(n log n) work if there is no helpful index. If you already have an index that matches the key and order, many engines can do less sorting and the job gets much cheaper. The delete itself is I/O heavy because every removed row must be logged and every index entry must be updated; for multi-million-row tables, deleting in batches of roughly 5,000 to 20,000 rows per transaction is often easier on the database.

Watch out for NULL values: rows with NULL in the duplicate key may be grouped together by the window function, which is often fine but not always what the business wants. Also watch out for ties in the ORDER BY; if two duplicates look equal, add another column such as the primary key so the winner is predictable. If there is no primary key at all, use a surrogate key in the future, or a database-specific row locator such as PostgreSQL ctid for a one-time cleanup.

Real-World Example: In a subscription checkout service, the customers table may get duplicate email rows when retries happen during a network timeout. The dedupe job should keep the earliest verified profile, delete the later retry rows, and then add a unique constraint so the same email cannot be inserted twice again. If a developer uses plain SELECT DISTINCT in a report and assumes the table is fixed, the UI looks clean while the underlying table still contains duplicates; later, users receive two welcome emails, support sees repeated receipts, and logs show repeated inserts or unique-key failures after the cleanup script runs inconsistently.

Follow-up & Tricky Questions:

• How do you choose which row to keep? Pick a clear business rule such as oldest, newest, or most complete row. Then put that rule in the ORDER BY so the result is deterministic.
• What if duplicates are defined by multiple columns? Put all of those columns into PARTITION BY. That makes each unique business identity its own group.
• How do you prevent duplicates from coming back? Add a unique constraint or unique index on the business key after cleanup. Cleanup fixes old data; constraints protect new data.
• How would you handle a very large table? Use the same ranking logic, but delete in batches or rebuild a clean table and swap it in. That reduces long locks and makes rollback safer.
• How do you prove the cleanup worked? Run a grouped count query after the delete. If GROUP BY key HAVING COUNT(*) > 1 returns no rows, the duplicates are gone.
• Tricky: Does SELECT DISTINCT remove duplicates from the table? No. It only removes duplicates from the query result; the stored rows remain unchanged.
• Tricky: Can I delete duplicates without a tie-breaker? Not safely. If two rows are identical on the sort columns, the chosen survivor can be unpredictable, so always add a stable final column like the primary key.
• Tricky: Are NULL values always kept separate? Not in this pattern. In window partitioning, NULLs are grouped together for deduping, so decide whether that matches your business rule before you run the delete.

Common Mistakes:

• Using SELECT DISTINCT and calling it cleanup. Correction: it only changes the result set, not the table.
• Forgetting the tie-breaker. Correction: add a stable column like the primary key so the same row always wins.
• Defining duplicates too broadly or too narrowly. Correction: use the real business key, not just whatever column looks convenient.
• Deleting in one huge shot on a busy table. Correction: batch the work or rebuild into a clean table to reduce locks and log pressure.

Memory Hook: Think of duplicates like a stack of identical shipping labels: pick one label to keep on the package, and recycle the rest.

Cheat Sheet:

• Define the duplicate key first.
• Use ROW_NUMBER() with PARTITION BY.
• Order by the row you want to keep.
• Delete rows where rn > 1.
• Verify with GROUP BY ... HAVING COUNT(*) > 1.
• Add a unique constraint to stop future duplicates.

Practice Tasks:

• Change the example to keep the newest row instead of the oldest.
• Deduplicate by (first_name, last_name, dob) instead of email.
• Write a query that only shows duplicate groups before you delete them.