How do you find duplicate rows in a table?
Quick Answer
Group by the columns that define a duplicate and keep groups with more than one row: SELECT Email, COUNT(*) FROM Users GROUP BY Email HAVING COUNT(*) > 1;. To see the actual rows, use ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Id) and filter where it's greater than 1.
The simplest is GROUP BY the columns that make a row a duplicate — say email — with HAVING COUNT(*) > 1, which gives me the duplicated values and how many. If I need the actual rows, I use ROW_NUMBER partitioned by email in a CTE and filter where the number is greater than 1 — the ordering in the window decides which copy counts as the original and which are the extras.
- Just the duplicate keys and counts:
GROUP BY ... HAVING COUNT(*) > 1. - The full duplicate rows (all but the first):
WITH D AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Id) rn FROM Users) SELECT * FROM D WHERE rn > 1; - Choose the
ORDER BYin the window to decide which copy is “the keeper” (oldest, newest, most complete).