SQL Query to Find and Remove Duplicate Records

Duplicate rows are one of the most common data quality problems in production databases — they creep in from failed retries, missing unique constraints, or bad ETL jobs. This guide covers the exact SQL patterns to detect duplicates, count how many exist, and safely remove them in MySQL and PostgreSQL, without accidentally deleting rows you meant to keep.

Sample table

Assume a customers table where the same email address was accidentally inserted more than once:

customers
------------------------
id      INT PRIMARY KEY
name    VARCHAR(100)
email   VARCHAR(150)
city    VARCHAR(50)

Step 1: Find duplicate values with GROUP BY

The simplest way to find duplicates is to group rows by the column that should be unique, then filter groups that appear more than once using HAVING:

SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

This returns each duplicated email along with how many times it occurs. It tells you there is a problem, but not which specific row IDs are duplicates.

Step 2: Show the actual duplicate rows

To see the full rows involved, join the grouped result back to the original table:

SELECT c.*
FROM customers c
JOIN (
  SELECT email
  FROM customers
  GROUP BY email
  HAVING COUNT(*) > 1
) dupes ON c.email = dupes.email
ORDER BY c.email, c.id;

Step 3: Delete duplicates using ROW_NUMBER()

The safest way to delete duplicates — supported in PostgreSQL, MySQL 8+, and SQL Server — is to rank rows within each duplicate group using ROW_NUMBER() and a PARTITION BY clause, keeping only the first occurrence:

WITH ranked AS (
  SELECT
    id,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY id ASC
    ) AS rn
  FROM customers
)
DELETE FROM customers
WHERE id IN (
  SELECT id FROM ranked WHERE rn > 1
);

Here, PARTITION BY email groups rows by the duplicate key, and ORDER BY id ASC keeps the row with the smallest id (the oldest record) and marks every later duplicate with rn greater than 1 for deletion.

Step 4: Prevent duplicates from coming back

Once duplicates are cleaned up, add a UNIQUE constraint so the problem cannot recur:

ALTER TABLE customers
ADD CONSTRAINT unique_email UNIQUE (email);

Best practices to avoid re-introducing duplicates:

• Always run duplicate-finding SELECT queries before running any DELETE.
• Wrap DELETE statements in a transaction (BEGIN / COMMIT) so you can roll back if the result is wrong.
• Take a backup or export the affected rows before deleting in production.
• Use INSERT ... ON CONFLICT DO NOTHING (PostgreSQL) or INSERT IGNORE (MySQL) once a unique constraint exists.

Generating this query without writing it by hand

If you would rather describe the problem in plain English — "find duplicate emails in the customers table and keep only the oldest row" — an AI SQL generator can produce the ROW_NUMBER() query above instantly, adapted to your exact table and column names.

Frequently Asked Questions