Finding and Removing Duplicates

"Find the duplicates, then delete all but one" is one of the most reliably asked SQL questions, because it tests grouping, window functions and DELETE semantics in a single short problem.

Finding duplicates

Group by whatever defines "the same", and keep groups with more than one row:

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

For a duplicate defined by several columns, list them all — the group key is the definition of duplication:

SELECT first_name, last_name, date_of_birth, COUNT(*) AS copies
FROM patients
GROUP BY first_name, last_name, date_of_birth
HAVING COUNT(*) > 1;

To see the offending rows rather than just the keys, feed the groups back in — and note that a window function does it in one pass:

-- Two passes
SELECT * FROM users
WHERE email IN (
    SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1
);

-- One pass
SELECT * FROM (
    SELECT u.*, COUNT(*) OVER (PARTITION BY email) AS copies
    FROM users u
) t
WHERE copies > 1;

Deleting all but one

The standard answer, and the one to reach for: number the rows inside each duplicate group and delete everything numbered above 1.

WITH numbered AS (
    SELECT id,
           ROW_NUMBER() OVER (
               PARTITION BY email          -- what makes rows duplicates
               ORDER BY created_at, id     -- which copy to KEEP: the first
           ) AS rn
    FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM numbered WHERE rn > 1);

Two decisions to state out loud, because the interviewer is listening for them:

• `PARTITION BY` defines duplication.
• `ORDER BY` decides which copy survives — usually the oldest, or the one with the

most complete data. Without a deterministic order, which row survives is arbitrary.

PostgreSQL and SQL Server allow deleting directly from a CTE; MySQL 8 needs the DELETE … WHERE id IN (SELECT …) form shown, and older MySQL versions reject selecting from the same table you are deleting from — the workaround is to wrap the subquery in another derived table:

DELETE FROM users
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn FROM users
    ) x WHERE rn > 1
);

Without window functions

The classic pre-window answer, still worth knowing: keep the minimum id per group.

DELETE FROM users
WHERE id NOT IN (
    SELECT keep_id FROM (
        SELECT MIN(id) AS keep_id FROM users GROUP BY email
    ) survivors
);

-- Or as a self-join: delete any row that has a smaller twin
DELETE u1 FROM users u1
JOIN users u2 ON u1.email = u2.email AND u1.id > u2.id;

The self-join version is the neat one — u1.id > u2.id means "there exists an earlier row with the same email", which is exactly the definition of a redundant copy.

When there is no unique key

A table with genuinely identical rows and no id has nothing to target. Options:

-- PostgreSQL: every row has a hidden physical identifier
DELETE FROM logs a USING logs b
WHERE a.ctid > b.ctid AND a.message = b.message AND a.logged_at = b.logged_at;

-- Portable: rebuild the table from its distinct rows
CREATE TABLE logs_clean AS SELECT DISTINCT * FROM logs;
DROP TABLE logs;
ALTER TABLE logs_clean RENAME TO logs;

The rebuild is often the better answer on a large table anyway — bulk DELETE generates far more write-ahead log and leaves the table bloated until it is vacuumed.

Preventing them

The answer the interviewer usually wants after the cleanup:

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

-- And handle the collision at write time instead of cleaning up later
INSERT INTO users (email, name) VALUES ('a@b.com', 'Ana')
ON CONFLICT (email) DO NOTHING;                    -- PostgreSQL
INSERT ... ON DUPLICATE KEY UPDATE name = VALUES(name);   -- MySQL
MERGE INTO ...                                     -- SQL Server, Oracle, standard

Note that a UNIQUE constraint still permits multiple NULLs in most engines — see NULL handling.

Mistakes that cost the interview

• `DELETE` with no `ORDER BY` in the `ROW_NUMBER`, so which copy survives is arbitrary.
• `NOT IN` over a subquery that can contain NULL — the whole delete matches nothing.
• Grouping by too few columns, deleting rows that were not actually duplicates.
• Forgetting the MySQL same-table restriction.
• Cleaning up without adding the constraint, so the duplicates come back.
• Running the `DELETE` before running the equivalent `SELECT`. Always look at the rows

first.

Practise these on the SQL sheet.