On this page
- The table used in every example
- 1. How do you find the duplicate rows?
- 2. When is SELECT DISTINCT enough?
- 3. How do you keep 1 row per person?
- 4. Why does the MAX-and-join method bring duplicates back?
- 5. How do you delete the duplicates safely?
- 6. How do you stop duplicates coming back?
- A checklist to run before you delete anything
Someone asks why the marketing report shows 8 sign-ups when the sales team can only name 4 customers. The signups table has repeated rows, and before you delete anything you need to know what kind of repeat each one is. Delete by the wrong rule and you remove the newest, correct row and keep the stale one.
This guide uses one small table so you can check every result by eye. The queries ran in DuckDB, and the output under each query is what it printed. The SQL is standard and works the same way in PostgreSQL, Snowflake, BigQuery and SQL Server unless a note says otherwise.
The table used in every example
8 rows, four real people. Three things are on purpose: rows 1 and 2 are the same sign-up loaded twice, rows 5, 6 and 8 are 3 versions of Meena's record, and row 4 is Ravi again with different capital letters in his email.
CREATE TABLE signups (
row_id INTEGER,
email VARCHAR,
name VARCHAR,
city VARCHAR,
updated_at TIMESTAMP
);
INSERT INTO signups VALUES
(1, '[email protected]', 'Asha', 'Pune', '2026-03-01 09:00'),
(2, '[email protected]', 'Asha', 'Pune', '2026-03-01 09:00'),
(3, '[email protected]', 'Ravi', 'Pune', '2026-03-02 10:00'),
(4, '[email protected]', 'Ravi', 'Mumbai', '2026-03-05 11:30'),
(5, '[email protected]', 'Meena', 'Chennai', '2026-03-03 08:00'),
(6, '[email protected]', 'Meena', 'Coimbatore', '2026-03-04 12:00'),
(7, '[email protected]', 'Kiran', 'Chennai', '2026-03-04 15:00'),
(8, '[email protected]', 'Meena', 'Coimbatore', '2026-03-04 12:00');SELECT * FROM signups ORDER BY row_id;row_id | email | name | city | updated_at -------+----------------+-------+------------+-------------------- 1 | [email protected] | Asha | Pune | 2026-03-01 09:00:00 2 | [email protected] | Asha | Pune | 2026-03-01 09:00:00 3 | [email protected] | Ravi | Pune | 2026-03-02 10:00:00 4 | [email protected] | Ravi | Mumbai | 2026-03-05 11:30:00 5 | [email protected] | Meena | Chennai | 2026-03-03 08:00:00 6 | [email protected] | Meena | Coimbatore | 2026-03-04 12:00:00 7 | [email protected] | Kiran | Chennai | 2026-03-04 15:00:00 8 | [email protected] | Meena | Coimbatore | 2026-03-04 12:00:00 (8 rows)
row_id is only a label the loader gave each row. It is not part of the person, so it cannot tell you which rows are the same person. The email can, once you deal with the capital letters.
Rows 1 and 2: [email protected], Asha, Pune, the same time.
Rows 5, 6 and 8: Meena in Chennai, then Coimbatore twice.
Fix: ROW_NUMBER, keep the latestRows 3 and 4: [email protected] and [email protected].
How do you find the duplicate rows?
Group by the column that identifies a person, count the rows in each group, and keep the groups with more than one.
SELECT email, COUNT(*) AS copies
FROM signups
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;email | copies ---------------+------- [email protected] | 2 [email protected] | 3 (2 rows)
Asha and Meena show up. Ravi does not, even though he has 2 rows, because [email protected] and [email protected] are different strings to the database. Normalise the key first, with LOWER and TRIM, and group on that:
SELECT LOWER(TRIM(email)) AS email_key,
COUNT(*) AS copies,
MIN(row_id) AS first_row,
MAX(row_id) AS last_row
FROM signups
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
ORDER BY email_key;email_key | copies | first_row | last_row ---------------+--------+-----------+--------- [email protected] | 2 | 1 | 2 [email protected] | 3 | 5 | 8 [email protected] | 2 | 3 | 4 (3 rows)
Now all three people with repeated rows are found. Whenever a duplicate check "finds nothing" on data you know is messy, look at the spelling of the key before you trust the result. A trailing space does the same thing as a capital letter, which is why TRIM is in the key.
When is SELECT DISTINCT enough?
DISTINCT removes rows that are identical in every selected column. That is exactly the case for rows 1 and 2, and not for anything else. Compare four counts:
SELECT COUNT(*) AS rows_in_table,
(SELECT COUNT(*) FROM (SELECT DISTINCT email, name, city, updated_at FROM signups)) AS distinct_without_row_id,
(SELECT COUNT(*) FROM (SELECT DISTINCT * FROM signups)) AS distinct_with_row_id,
(SELECT COUNT(DISTINCT LOWER(TRIM(email))) FROM signups) AS real_people
FROM signups;rows_in_table | distinct_without_row_id | distinct_with_row_id | real_people --------------+-------------------------+----------------------+------------ 8 | 6 | 8 | 4 (1 row)
Leaving row_id out collapses the two exact copies of Asha and the two identical Meena rows, so 8 becomes 6. SELECT DISTINCT * changes nothing, because row_id is different on every row and so no 2 rows are ever identical. And 6 is still not 4: Meena has two different cities on file, and Ravi's 2 rows differ in city and spelling. DISTINCT cannot choose between conflicting versions. It only removes copies.
How do you keep 1 row per person?
When rows disagree, you need a rule for which one wins. Here the rule is "the most recent update". Number the rows inside each person, newest first, and keep number 1. The PARTITION BY is the normalised key, and the ORDER BY carries the rule.
PARTITION BY LOWER(TRIM(email)), ORDER BY updated_at DESC, row_id DESC. Partition: [email protected]
Coimbatore, 4 Mar 12:00
Coimbatore, 4 Mar 12:00
Same time as row 8, so row_id broke the tie.
Chennai, 3 Mar 08:00
SELECT row_id, LOWER(TRIM(email)) AS email_key, city, updated_at,
ROW_NUMBER() OVER (
PARTITION BY LOWER(TRIM(email))
ORDER BY updated_at DESC, row_id DESC
) AS rn
FROM signups
ORDER BY email_key, rn;row_id | email_key | city | updated_at | rn -------+----------------+------------+---------------------+--- 2 | [email protected] | Pune | 2026-03-01 09:00:00 | 1 1 | [email protected] | Pune | 2026-03-01 09:00:00 | 2 7 | [email protected] | Chennai | 2026-03-04 15:00:00 | 1 8 | [email protected] | Coimbatore | 2026-03-04 12:00:00 | 1 6 | [email protected] | Coimbatore | 2026-03-04 12:00:00 | 2 5 | [email protected] | Chennai | 2026-03-03 08:00:00 | 3 4 | [email protected] | Mumbai | 2026-03-05 11:30:00 | 1 3 | [email protected] | Pune | 2026-03-02 10:00:00 | 2 (8 rows)
Every row now has a position inside its person. Rows with rn = 1 are the keepers. Look at Meena: rows 6 and 8 have the same updated_at, so without row_id DESC the database could number them either way, and a rerun on a bigger table might keep the other one. The tie-breaker makes the result repeatable. Filter on rn = 1 in an outer query, because a window function cannot sit in WHERE:
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY LOWER(TRIM(email))
ORDER BY updated_at DESC, row_id DESC
) AS rn
FROM signups
)
SELECT row_id, email, name, city, updated_at
FROM ranked
WHERE rn = 1
ORDER BY row_id;row_id | email | name | city | updated_at -------+----------------+-------+------------+-------------------- 2 | [email protected] | Asha | Pune | 2026-03-01 09:00:00 4 | [email protected] | Ravi | Mumbai | 2026-03-05 11:30:00 7 | [email protected] | Kiran | Chennai | 2026-03-04 15:00:00 8 | [email protected] | Meena | Coimbatore | 2026-03-04 12:00:00 (4 rows)
4 rows, one per person, each the latest. Ravi's newer Mumbai row beat his older Pune row, and Meena kept Coimbatore. Snowflake, BigQuery, Databricks SQL and DuckDB can drop the CTE with QUALIFY, which filters on a window function directly:
SELECT row_id, email, name, city, updated_at
FROM signups
QUALIFY ROW_NUMBER() OVER (
PARTITION BY LOWER(TRIM(email))
ORDER BY updated_at DESC, row_id DESC
) = 1
ORDER BY row_id;row_id | email | name | city | updated_at -------+----------------+-------+------------+-------------------- 2 | [email protected] | Asha | Pune | 2026-03-01 09:00:00 4 | [email protected] | Ravi | Mumbai | 2026-03-05 11:30:00 7 | [email protected] | Kiran | Chennai | 2026-03-04 15:00:00 8 | [email protected] | Meena | Coimbatore | 2026-03-04 12:00:00 (4 rows)
PostgreSQL and MySQL do not have QUALIFY, so write the CTE version in an interview and mention QUALIFY as the shorter form where it exists.
Why does the MAX-and-join method bring duplicates back?
A second common answer finds each person's latest timestamp with MAX and joins back to the table to fetch the whole row. It looks right and it has a hole.
SELECT s.row_id, s.email, s.city, s.updated_at
FROM signups s
JOIN (
SELECT LOWER(TRIM(email)) AS email_key, MAX(updated_at) AS latest
FROM signups
GROUP BY LOWER(TRIM(email))
) m
ON LOWER(TRIM(s.email)) = m.email_key
AND s.updated_at = m.latest
ORDER BY s.row_id;row_id | email | city | updated_at -------+----------------+------------+-------------------- 1 | [email protected] | Pune | 2026-03-01 09:00:00 2 | [email protected] | Pune | 2026-03-01 09:00:00 4 | [email protected] | Mumbai | 2026-03-05 11:30:00 6 | [email protected] | Coimbatore | 2026-03-04 12:00:00 7 | [email protected] | Chennai | 2026-03-04 15:00:00 8 | [email protected] | Coimbatore | 2026-03-04 12:00:00 (6 rows)
6 rows came back, not four. Asha's 2 copies share the same timestamp, so both match her MAX, and so do Meena's rows 6 and 8. The join matched on a value, and 2 rows can hold the same value. ROW_NUMBER always returns exactly 1 row per group because it numbers the rows. MAX plus a join returns every row that ties for the top.
How do you delete the duplicates safely?
Deleting is different from selecting, because you cannot undo a wrong DELETE. Take a copy first, then delete by row_id, the 1 column that points at exactly 1 row.
CREATE TABLE signups_backup AS SELECT * FROM signups;
DELETE FROM signups
WHERE row_id IN (
SELECT row_id
FROM (
SELECT row_id,
ROW_NUMBER() OVER (
PARTITION BY LOWER(TRIM(email))
ORDER BY updated_at DESC, row_id DESC
) AS rn
FROM signups
) ranked
WHERE rn > 1
);Check the counts before you trust it. The backup should have all 8 rows, and the table should have one per person:
SELECT (SELECT COUNT(*) FROM signups_backup) AS rows_before,
(SELECT COUNT(*) FROM signups) AS rows_after,
(SELECT COUNT(DISTINCT LOWER(TRIM(email))) FROM signups) AS people_after;rows_before | rows_after | people_after ------------+------------+------------- 8 | 4 | 4 (1 row)
SELECT * FROM signups ORDER BY row_id;row_id | email | name | city | updated_at -------+----------------+-------+------------+-------------------- 2 | [email protected] | Asha | Pune | 2026-03-01 09:00:00 4 | [email protected] | Ravi | Mumbai | 2026-03-05 11:30:00 7 | [email protected] | Kiran | Chennai | 2026-03-04 15:00:00 8 | [email protected] | Meena | Coimbatore | 2026-03-04 12:00:00 (4 rows)
The survivors are rows 2, 4, 7 and 8, exactly the rn = 1 rows from section 3, and the counts agree: 8 rows before, 4 after, 4 people. Two habits make this delete safe. It deletes by row_id, so it cannot hit a row that merely looks similar. And it ranks and deletes in one statement, so the numbering cannot drift between a SELECT and a later DELETE.
How do you stop duplicates coming back?
Cleaning once is only half the job. If the loader runs again tomorrow, the duplicates return. A PRIMARY KEY or UNIQUE constraint makes the database refuse the second copy:
CREATE TABLE signups_unique (
email VARCHAR PRIMARY KEY,
name VARCHAR,
city VARCHAR
);
INSERT INTO signups_unique VALUES ('[email protected]', 'Asha', 'Pune');INSERT INTO signups_unique VALUES ('[email protected]', 'Asha', 'Mumbai');Constraint Error: Duplicate key "email: [email protected]" violates primary key constraint.
The same email is refused. But the constraint compares strings exactly, so it cannot see that [email protected] is the same person:
INSERT INTO signups_unique VALUES ('[email protected]', 'Asha', 'Mumbai');SELECT email, city FROM signups_unique ORDER BY email;email | city --------------+------- [email protected] | Mumbai [email protected] | Pune (2 rows)
2 rows for Asha again. Store the cleaned key (LOWER(TRIM(email))) in the column the constraint protects, and clean it in the load step so the database only ever sees one spelling.
Warehouses add a second catch. Snowflake and BigQuery accept PRIMARY KEY declarations but do not enforce them, so a duplicate row loads without any error. There the protection has to be in the pipeline: load into a staging table, apply the ROW_NUMBER step above, and then insert or MERGE only the winners.
A checklist to run before you delete anything
- What identifies one real thing? Write the key down, and normalise its spelling (
LOWER,TRIM). - Which kind of duplicate is it: an exact copy, a conflicting version, or a spelling variant?
- Which row should win, and what breaks a tie? Put both rules in the
ORDER BY. - Count rows before and after, and compare with the number of distinct keys you expected.
- Keep a backup or the rejected rows until someone has looked at the result.
- Add the constraint or the staging step that stops the next load from repeating the problem.
Practise the pattern in the browser with Deduplicate events with ROW_NUMBER (free), then try the delete form in Delete duplicates, keep the lowest id. For ROW_NUMBER, RANK and ties in more depth, read the window function guide.