Answer
Find Duplicate Records in SQL
Sample Table: customers
SQL
| id | name | email | phone |
|----|---------|-------------------|-----------|
| 1 | Alice | alice@test.com | 9876543210 |
| 2 | Bob | bob@test.com | 9123456789 |
| 3 | Alice | alice@test.com | 9876543210 | ← duplicate of row 1
| 4 | Charlie | charlie@test.com | 9999999999 |
| 5 | Bob | bob@test.com | 9123456789 | ← duplicate of row 2
Method 1: GROUP BY + HAVING (Most Common)
SQL
-- Find duplicate emails
SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;
-- Result:
-- alice@test.com | 2
-- bob@test.com | 2
-- Find duplicates across multiple columns
SELECT name, email, COUNT(*) AS count
FROM customers
GROUP BY name, email
HAVING COUNT(*) > 1;
Method 2: Show the Actual Duplicate Rows
SQL
-- Using a subquery to show all columns of duplicate rows
SELECT *
FROM customers
WHERE email IN (
SELECT email
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
)
ORDER BY email;
-- Result: all 4 duplicate rows shown (ids 1,2,3,5)
Method 3: ROW_NUMBER() to Identify Which to Delete
SQL
-- Assign row number within each duplicate group
-- Keep row_num = 1 (first occurrence), delete rest
WITH ranked AS (
SELECT id, name, email,
ROW_NUMBER() OVER (
PARTITION BY email -- group duplicates
ORDER BY id ASC -- keep lowest id
) AS rn
FROM customers
)
SELECT * FROM ranked;
-- Result:
-- 1 | Alice | alice@test.com | 1 ← keep
-- 3 | Alice | alice@test.com | 2 ← delete
-- 2 | Bob | bob@test.com | 1 ← keep
-- 5 | Bob | bob@test.com | 2 ← delete
Delete Duplicate Rows (Keep One)
SQL
-- MySQL: delete duplicates, keep the lowest id
DELETE c1
FROM customers c1
JOIN customers c2
ON c1.email = c2.email
AND c1.id > c2.id; -- keep lower id, delete higher
-- PostgreSQL / SQL Server: using CTE + ROW_NUMBER
WITH ranked AS (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
Count Total Duplicates
SQL
-- How many duplicate rows exist?
SELECT COUNT(*) - COUNT(DISTINCT email) AS duplicate_count
FROM customers;
-- COUNT(*) = 5 (all rows)
-- COUNT(DISTINCT email) = 3 (unique emails)
-- Duplicates = 5 - 3 = 2
In QA — Validate No Duplicates After Test Run
SQL
-- Assert unique constraint not violated after data load
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Expected: 0 rows (no duplicates)
-- Assert test data seeded correctly (no accidental duplicates)
SELECT test_case_id, COUNT(*) AS cnt
FROM test_results
WHERE test_run_id = 'RUN-001'
GROUP BY test_case_id
HAVING COUNT(*) > 1;
-- Expected: 0 rows