Find duplicate rows
The GROUP BY / HAVING idiom every deduplication starts with: group on the column that should be unique, keep the groups bigger than one.
Last updated
SELECT email, COUNT(*) AS copies FROM users GROUP BY email HAVING COUNT(*) > 1 ORDER BY copies DESC;
Why it’s written this way
GROUP BY collapses the table to one row per distinct email, COUNT(*) says how many source rows each group absorbed, and HAVING filters groups after aggregation — which is exactly why the condition cannot go in WHERE, which runs before it.
ORDER BY copies DESC puts the worst offenders first, which is what you want when the answer decides a cleanup.
Edge cases to know
- →A double insert and two accounts that share an address look identical here: both report copies = 2. Add COUNT(DISTINCT id) to tell them apart before deleting anything.
- →NULLs group together — a column full of NULLs reports as one big duplicate group. Filter WHERE email IS NOT NULL if that is noise.
- →Case matters: A@example.com and a@example.com are different groups unless you GROUP BY lower(email).
Part by part
- COUNT(*) AS copies
- Counts rows in each group. Naming it makes the column usable in ORDER BY and readable in the output.
- GROUP BY email
- Defines what counts as a duplicate. Everything about this query follows from the choice of column here.
- HAVING COUNT(*) > 1
- Filters the groups after aggregation. WHERE cannot do this, because WHERE runs before rows are grouped and COUNT does not exist yet.
- ORDER BY copies DESC
- Puts the worst offenders first, which is what you want when the list is long enough that you will only work through part of it.
Common mistakes
SELECT email, COUNT(*) FROM users WHERE COUNT(*) > 1 GROUP BY email;WHERE is evaluated before grouping, so the aggregate does not exist at that point and the database rejects the query. Filtering on an aggregate is exactly what HAVING is for.
SELECT email, COUNT(*) AS copies FROM users GROUP BY email HAVING COUNT(*) > 1 ORDER BY copies DESC;SELECT DISTINCT email FROM users;This lists the distinct values and tells you nothing about which of them repeat or how often. It answers a different question and is easy to reach for by reflex.
GROUP BY email HAVING COUNT(*) > 1 on a case-sensitive columnAda@example.com and ada@example.com group separately in a case-sensitive collation, so genuine duplicates are missed. Group by LOWER(email) when the column is case-sensitive but the data is not meant to be.
Questions
How do I see the actual duplicate rows, not just the counts?
Join this result back to the table, or use a window function: COUNT(*) OVER (PARTITION BY email) adds the count to every row without collapsing them, so you can filter for counts above one and still see each full record.
Why HAVING rather than WHERE?
Because they run at different stages. WHERE filters individual rows before grouping; HAVING filters the groups after aggregation. Any condition mentioning an aggregate has to be in HAVING.
How do I find rows duplicated across several columns?
List them all in the GROUP BY: GROUP BY first_name, last_name, birth_date. Be careful with nullable columns, because NULL values group together in most databases but never compare equal in a join.
Is this slow on a large table?
It has to read and group every row, so it is proportional to table size. An index on the grouped column lets the database group without a full sort, which is usually the difference between seconds and minutes.