How to remove duplicate rows in SQL

See them before you touch them

Start with a count per key, so you know the size of the problem and can eyeball whether the key is right:

SELECT lower(trim(email)) AS k, COUNT(*) AS n
FROM contacts
GROUP BY 1
HAVING COUNT(*) > 1
ORDER BY n DESC;

Normalising inside the key (lower, trim) is the difference between finding the duplicates and finding half of them. If the table is large and you will run this often, an index on the same expression pays for itself.

SELECT DISTINCT, and its one trap

SELECT DISTINCT email FROM contacts; gives unique addresses. The trap is that DISTINCT applies to the entire select list, so SELECT DISTINCT id, email returns every row unchanged, because the id makes each pair unique. If you need other columns alongside a unique key, DISTINCT is the wrong tool and ROW_NUMBER is the right one.

Keep one row per group with ROW_NUMBER

SELECT *
FROM (
  SELECT c.*,
         ROW_NUMBER() OVER (
           PARTITION BY lower(trim(c.email))
           ORDER BY c.created_at DESC, c.id
         ) AS rn
  FROM contacts c
) t
WHERE rn = 1;

The ORDER BY inside OVER decides which row survives: newest here, by created_at. Always end it with a unique column such as the primary key, or ties break arbitrarily and two runs can keep different rows.

Deleting, safely

Run the SELECT version first, check the row count, and wrap the delete in a transaction you can roll back.

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY lower(trim(email))
           ORDER BY created_at DESC, id
         ) AS rn
  FROM contacts
)
DELETE FROM contacts
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

Dialect notes: that form works in PostgreSQL. SQL Server lets you delete through the CTE directly, so WITH ranked AS (SELECT *, ROW_NUMBER() ... FROM contacts) DELETE FROM ranked WHERE rn > 1; is idiomatic there. MySQL 8 has window functions but refuses a subquery that reads the table being deleted, so wrap the ranking in a derived table (... WHERE id IN (SELECT id FROM (SELECT ...) AS t WHERE rn > 1)). SQLite has no id requirement: partition on your key and delete by rowid.

If the table has no unique column at all, PostgreSQL's system column ctid works as a per-row address, and in most engines the safest fallback is to build a clean table with a CREATE TABLE ... AS SELECT and swap it in.

Do you need a tool for this? Probably not

Be honest about it: if your data is in a table and you can write SQL, nothing on this site beats the queries above. They are exact, repeatable, and run where the data already lives. The case we exist for is the other one, where a CSV lands in your inbox and creating a table, loading it, and dropping it is more work than the cleanup itself. For that, the in-browser CSV tool does column-selected exact dedupe locally, and fuzzy matching in the API handles the near-misses SQL equality never will. Working in Python instead? See removing duplicates with pandas.

Last updated July 27, 2026