How do I find duplicate rows in a table?
You group by the columns that should be unique, and you keep the groups that have more than one row.
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
email VARCHAR(200) NOT NULL,
full_name VARCHAR(120) NOT NULL
);
So the shape of it is always the same. The group by says which columns should be unique, and the HAVING clause keeps the groups where the count is more than one.
-- The values that appear more than once.
SELECT email, COUNT(*) AS times_seen
FROM accounts
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY times_seen DESC;
And if you want the whole rows rather than just the count, you join the table back to the groups.
-- Every row that is part of a duplicate group.
SELECT a.*
FROM accounts a
JOIN (
SELECT email FROM accounts GROUP BY email HAVING COUNT(*) > 1
) AS dupes ON dupes.email = a.email
ORDER BY a.email, a.id;
There is one detail worth knowing about that. If a group has three rows with the same email, the join brings back all three of them, and not two. That is usually what you want, because you want to look at every row that is involved before you decide which one to keep.
The next two questions in this topic ask for a version that finds the duplicates and then cleans them up.