How do you find duplicate rows in a table?
The standard approach is to GROUP BY the columns that should be unique and HAVING COUNT(*) > 1 to keep only the duplicates.
Find duplicates —
GROUP BYon candidate columns +HAVING COUNT(*) > 1.See full duplicate rows — join the grouped result back to the table or use
ROW_NUMBER() OVER (PARTITION BY ... ).Delete duplicates, keep one — use
ROW_NUMBER()in a CTE and delete rows where rank > 1.Prevent duplicates — add a UNIQUE constraint or unique index.
-- Find duplicate emails
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Find duplicate rows with full details
SELECT u.*
FROM users u
INNER JOIN (
SELECT email, MIN(id) AS min_id
FROM users
GROUP BY email
HAVING COUNT(*) > 1
) dups ON u.email = dups.email;
-- Find duplicates across multiple columns
SELECT first_name, last_name, COUNT(*)
FROM employees
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;GROUP BY email groups all rows with the same email. HAVING COUNT(*) > 1 keeps only groups with duplicates.
The JOIN version gets full row details for duplicates. Multi-column grouping finds name duplicates.
GROUP BY + HAVING COUNT(*) > 1 is the standard pattern. This is a top-5 SQL interview query.
Know how to get full row details (JOIN back to the table). The follow-up is always 'how do you DELETE duplicates keeping one?' — use DELETE with self-join and keep the row with MIN(id).