How do you find duplicate rows in a table?

Question

Accepted Answer

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 BY on 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. 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).