Find and remove duplicate rows in SQL

Duplicates usually arrive through a retried import, a form submitted twice or two systems feeding one table. This guide removes them safely and then stops them returning. The examples use a small customers table.

CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT, name TEXT, created_at TEXT);
INSERT INTO customers (email, name, created_at) VALUES
 ('ana@example.com', 'Ana',   '2025-01-03'),
 ('ana@example.com', 'Ana B', '2025-02-10'),
 ('ben@example.com', 'Ben',   '2025-01-20'),
 ('cy@example.com',  'Cy',    '2025-01-25'),
 ('cy@example.com',  'Cy',    '2025-01-25'),
 ('cy@example.com',  'Cyrus', '2025-03-01');

1. Find the duplicates

Group by whatever should be unique and keep only the groups with more than one row.

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

This returns ana@example.com with 2 copies and cy@example.com with 3. Run it first and read the result: it tells you whether you are about to touch 5 rows or 5 million.

2. Decide which copy survives, and number them

ROW_NUMBER() gives each row in a group a position. The ORDER BY inside the window is your rule: here the newest created_at wins, and id breaks a tie so the result is the same every time.

SELECT id, email, name, created_at,
       ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY created_at DESC, id DESC
       ) AS rn
FROM customers
ORDER BY email, rn;

The row with rn = 1 in each group is the one you keep. For cy@example.com that is the March row, Cyrus.

3. Delete everything else

Wrap the numbering in a subquery and delete the ids whose rn is greater than 1. Do this inside a transaction so you can check the result before you commit.

BEGIN;

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

SELECT id, email, name, created_at FROM customers ORDER BY email;
-- happy with it? COMMIT;   otherwise: ROLLBACK;

After the delete, three rows remain: Ana B (id 2), Ben (id 3) and Cyrus (id 6).

4. Make it impossible to happen again

Cleaning up is only half the job. A unique index makes the database refuse the next duplicate.

CREATE UNIQUE INDEX customers_email_unique ON customers (email);

Inserting ben@example.com again now fails with UNIQUE constraint failed: customers.email. Your import code should catch that error and decide what to do, for example update the existing row instead of adding a new one.

Things to watch

• Case and spaces. Ana@Example.com and ana@example.com are different strings. Clean them first (LOWER(TRIM(email))) or the duplicates will not be found.
• Foreign keys. If other tables point at the rows you delete, re-point them to the surviving id first.
• NULLs. Most databases allow several NULLs in a unique column, so rows with no email are not treated as duplicates of each other.