How Do You Delete Duplicate Rows in SQL?
Learn how to delete duplicate rows in SQL using ROW_NUMBER() and MIN(id), keep one copy, prevent recurrence, and ace this common SQL interview question.
Expected Interview Answer
You delete duplicate rows in SQL by keeping one representative row per duplicate group and removing the rest, typically with a window function like ROW_NUMBER() partitioned by the columns that define a duplicate, then deleting every row whose row number is greater than 1.
The robust modern approach wraps the table in a CTE that assigns ROW_NUMBER() OVER (PARTITION BY the duplicate-defining columns ORDER BY some tiebreaker) and deletes where that number > 1. If the table has a unique id you can instead delete rows whose id is not the MIN(id) for each group using a self-join or a correlated subquery. On tables with no unique key you must rely on the window-function method, ctid/rowid pseudo-columns, or rebuilding the table via SELECT DISTINCT.
- Removes redundant rows while preserving exactly one copy
- Window-function method works even without a primary key
- Deterministic when you supply an ORDER BY tiebreaker
- Improves data integrity and query accuracy
- Can be scoped to specific columns that define a duplicate
AI Mentor Explanation
Picture a scorer who accidentally logged the same delivery three times in the over. Before publishing the scorecard she numbers each identical entry 1, 2, 3, keeps entry number 1 as the official ball, and strikes out the rest so the runs are not double-counted. Deleting duplicate rows uses that exact ranking-then-remove move.
Step-by-Step Explanation
Step 1
Define what a duplicate is
List the exact columns whose repeated combination counts as a duplicate (e.g. email, or name plus dob).
Step 2
Rank rows per group
Use ROW_NUMBER() OVER (PARTITION BY those columns ORDER BY a tiebreaker like id) inside a CTE.
Step 3
Preview before deleting
SELECT the CTE and inspect rows where row_number > 1 to confirm you are removing the right copies.
Step 4
Delete the extras
DELETE FROM the CTE WHERE rn > 1, keeping exactly the row ranked 1 in each group.
Step 5
Prevent recurrence
Add a UNIQUE constraint or index on the duplicate-defining columns so future inserts cannot re-create duplicates.
What Interviewer Expects
- Knowledge of ROW_NUMBER() and PARTITION BY
- Awareness that a deterministic ORDER BY tiebreaker is needed
- An approach that works without a primary key
- Preventing future duplicates with a UNIQUE constraint
- Understanding of MIN(id) / self-join alternatives
Common Mistakes
- Using SELECT DISTINCT and thinking it deletes rows (it only filters output)
- Deleting every row in a duplicate group instead of keeping one
- Omitting ORDER BY so which row survives is nondeterministic
- Assuming a primary key always exists to key the delete
- Forgetting to add a constraint, so duplicates come straight back
Best Answer (HR Friendly)
“Duplicate rows are copies of the same record stored more than once. In SQL you number the copies within each group, keep one, and delete the rest — most commonly using the ROW_NUMBER() window function — then add a uniqueness rule so the duplicates cannot return.”
Code Example
WITH ranked AS (
SELECT
id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY id
) AS rn
FROM users
)
DELETE FROM users
WHERE id IN (
SELECT id FROM ranked WHERE rn > 1
);DELETE FROM users u
WHERE u.id > (
SELECT MIN(u2.id)
FROM users u2
WHERE u2.email = u.email
);Follow-up Questions
- How would you delete duplicates in a table with no unique key?
- What is the difference between DISTINCT and GROUP BY for finding duplicates?
- How do you find duplicates before deleting them?
- Why is an ORDER BY needed inside ROW_NUMBER() here?
- How would you prevent duplicates at insert time?
MCQ Practice
1. Which function is most commonly used to delete duplicate rows while keeping one?
ROW_NUMBER() partitioned by the duplicate-defining columns lets you keep row 1 and delete the rest.
2. Deleting rows where id > MIN(id) per group keeps which row?
MIN(id) is the smallest id in each group, so only that lowest-id row survives.
3. Why add ORDER BY inside ROW_NUMBER() when de-duplicating?
Without an ORDER BY tiebreaker, the engine may pick any row as number 1, making the result nondeterministic.
Flash Cards
Does SELECT DISTINCT delete duplicate rows? — No — it only removes duplicates from the query output; the table is unchanged.
Standard way to delete duplicates keeping one? — ROW_NUMBER() OVER (PARTITION BY dup columns) in a CTE, then DELETE WHERE rn > 1.
How to keep the earliest row per group? — Delete rows whose id is greater than MIN(id) for that group.
How to stop duplicates returning? — Add a UNIQUE constraint or unique index on the duplicate-defining columns.