How Do You Find Duplicate Records in SQL?
Learn how to find duplicate records in SQL using GROUP BY and HAVING COUNT, retrieve full duplicate rows with window functions, and delete extras safely.
Expected Interview Answer
You find duplicate records by grouping rows on the columns that define a duplicate and keeping only groups with more than one row, using GROUP BY with HAVING COUNT(*) > 1. This returns each duplicated value together with how many times it appears.
The core pattern is SELECT col, COUNT(*) FROM table GROUP BY col HAVING COUNT(*) > 1. You can group on several columns to define duplicates across a combination, such as email plus name. To retrieve the full duplicate rows (not just the keys), you can use a window function like COUNT(*) OVER (PARTITION BY col) and filter where the count exceeds one, or join the grouped result back to the original table. To then delete duplicates while keeping one copy, use ROW_NUMBER() OVER (PARTITION BY the duplicate columns ORDER BY id) and remove rows where the row number is greater than 1.
- GROUP BY with HAVING is portable and easy to read
- Works across single or multiple columns defining a duplicate
- Window functions can return full duplicate rows, not just keys
- ROW_NUMBER enables safe de-duplication keeping one copy
- Reveals how many times each value is duplicated via COUNT
AI Mentor Explanation
To spot players accidentally entered twice on a squad list, you group the entries by jersey number and flag any number that appears more than once. Each repeated number, along with its count, is a duplicate. That grouping-and-counting is exactly GROUP BY jersey HAVING COUNT(*) > 1 for finding duplicate records.
Step-by-Step Explanation
Step 1
Define what a duplicate is
Decide which column or columns must match for two rows to count as duplicates, such as email, or title plus year.
Step 2
Group by those columns
Write GROUP BY over exactly those columns so identical values collapse into one group.
Step 3
Count each group
Add COUNT(*) to the SELECT to measure how many rows fall into each group.
Step 4
Filter with HAVING
Keep only groups where COUNT(*) > 1, since those are the duplicated values.
Step 5
Fetch full rows if needed
Use COUNT(*) OVER (PARTITION BY the columns) and filter > 1, or join back, to see complete duplicate rows.
Step 6
De-duplicate if required
Use ROW_NUMBER() OVER (PARTITION BY the columns ORDER BY id) and delete rows where the row number exceeds 1 to keep one copy.
What Interviewer Expects
- The GROUP BY ... HAVING COUNT(*) > 1 pattern
- Understanding of grouping across multiple columns
- Difference between WHERE and HAVING
- How to retrieve full duplicate rows using window functions
- How to delete duplicates while keeping one copy with ROW_NUMBER
- Clarity on which columns define a duplicate
Common Mistakes
- Using WHERE instead of HAVING to filter on an aggregate count
- Grouping on the wrong columns so real duplicates are missed
- Assuming GROUP BY alone returns full rows rather than grouped keys
- Deleting all duplicate rows instead of keeping exactly one copy
- Forgetting that NULLs may not group the way you expect
Best Answer (HR Friendly)
“You group the rows by the columns that should be unique and then keep only the groups that appear more than once. In SQL that is GROUP BY those columns with HAVING COUNT(*) > 1, which lists each duplicated value and how many times it occurs.”
Code Example
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;-- See full duplicate rows
SELECT *
FROM (
SELECT u.*,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users u
) t
WHERE rn > 1;
-- Delete duplicates, keeping the lowest id
DELETE FROM users
WHERE id IN (
SELECT id FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users
) d
WHERE d.rn > 1
);Follow-up Questions
- How would you find duplicates across multiple columns?
- How do you delete duplicate rows while keeping one copy?
- What is the difference between WHERE and HAVING here?
- How would you return the full duplicate rows, not just the keys?
- How do NULL values behave when grouping for duplicates?
MCQ Practice
1. Which clause filters groups to keep only duplicated values?
HAVING filters on aggregate results after grouping, so HAVING COUNT(*) > 1 keeps only groups that appear more than once.
2. Which function helps delete duplicates while keeping exactly one row?
ROW_NUMBER() partitioned by the duplicate columns numbers each copy so you can delete rows where the number is greater than 1.
3. Why can't you use WHERE COUNT(*) > 1 to filter duplicates?
WHERE is evaluated before grouping and aggregation, so it cannot reference an aggregate like COUNT(*); that is what HAVING is for.
Flash Cards
Core query to find duplicates? — SELECT col, COUNT(*) FROM table GROUP BY col HAVING COUNT(*) > 1.
WHERE vs HAVING for duplicates? — WHERE filters rows before grouping; HAVING filters groups after aggregation, so aggregate filters use HAVING.
How to get full duplicate rows? — Use COUNT(*) OVER (PARTITION BY the columns) and filter > 1, or join the grouped result back to the table.
How to delete duplicates keeping one? — ROW_NUMBER() OVER (PARTITION BY the columns ORDER BY id) and delete rows where the row number is greater than 1.