Remove Duplicates, Keep the Latest in SQL

Example query

This example assumes: customers(id bigint, email text, updated_at timestamptz). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT id, email, updated_at
FROM (
  SELECT id,
         email,
         updated_at,
         ROW_NUMBER() OVER (
           PARTITION BY LOWER(TRIM(email))
           ORDER BY updated_at DESC, id DESC) AS rn
  FROM customers
  WHERE email IS NOT NULL
) ranked
WHERE rn > 1
ORDER BY email, updated_at DESC;

The delete itself (run only after reviewing the rows above, ideally inside a transaction with a backup)

DELETE FROM customers c
USING (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY LOWER(TRIM(email))
           ORDER BY updated_at DESC, id DESC) AS rn
  FROM customers
  WHERE email IS NOT NULL
) d
WHERE c.id = d.id
  AND d.rn > 1;

How it works

• ROW_NUMBER() restarts at 1 for each normalised email, newest row first.
• Row 1 is the one to keep; every row with rn > 1 is a duplicate, so the first query shows exactly what would be removed.
• The DELETE uses the same ranking and joins on id, so it removes only those rows.

MySQL, BigQuery, SQL Server and SQLite differences

• DELETE ... USING is PostgreSQL syntax. MySQL uses DELETE c FROM customers c JOIN (...) d ON c.id = d.id WHERE d.rn > 1; SQL Server can delete directly from a CTE that includes the ROW_NUMBER.

Common mistakes

• Other tables may reference the duplicate ids (orders, tickets); re-point them to the surviving row before deleting.
• Always run the SELECT first and count the rows; a wrong PARTITION BY deletes real records.
• Deletes cannot be undone without a backup. Make the change in a transaction or work on a copy.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• Show me the duplicate customer rows I would remove if I kept the newest for each email.
• How many duplicate contact records do we have?