PatternSQL
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
Pattern
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
- →To see the full duplicate rows rather than the counts, join this back to the table on email, or window it: COUNT(*) OVER (PARTITION BY email) > 1.
- →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@x.com and a@x.com are different groups unless you GROUP BY lower(email).