How to find duplicates
in SQL

Here is the mess we are working with. A customers table where the same person signed up twice under a slightly different name — the single most common way duplicates actually appear in production. Not identical rows. Identical keys with different payloads.

Note what this rules out. SELECT DISTINCT name, email will not help you: the names differ, so every row is already distinct. This is the trap that makes people think DISTINCT is broken.

1Which values are duplicated?

Group by the column that is supposed to be unique, then throw away every group that only has one member. That is all HAVING is for.

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

Composite keys work the same way. If a row is only a duplicate when several columns match, list them all: GROUP BY customer_id, product, order_date HAVING COUNT(*) > 1. On our sample orders table that returns zero rows — and that is the point of running it. An empty result here is the healthy answer, not a broken query.

2Which row should survive?

GROUP BY cannot answer this, and it is important to understand why: grouping collapses the rows. You get one output row per email, so the individual customer_id and name values are gone. You know a problem exists but you cannot act on it.

A window function keeps every row and adds a number alongside it. PARTITION BY is the grouping key; ORDER BY inside the OVER() decides which copy gets number 1.

WITH ranked AS (
  SELECT customer_id, name, email,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) AS rn,
         COUNT(*)     OVER (PARTITION BY email)                      AS copies
  FROM customers
)
SELECT customer_id, name, email, rn, copies
FROM ranked
WHERE copies > 1
ORDER BY email, rn

Now you can see the decision you are actually making. ORDER BY customer_id keeps the oldest record — "Daniel Martinez", the original signup. Change it to ORDER BY customer_id DESC and you keep "Dan Martinez" instead. Neither is automatically right. Newest wins if the later row is a correction; oldest wins if it is an accidental re-signup. That choice is a business decision wearing an ORDER BY costume.

WITH ranked AS (
  SELECT customer_id, name, email,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) AS rn
  FROM customers
)
SELECT customer_id, name, email
FROM ranked
WHERE rn = 1
ORDER BY customer_id

3The 10-second sanity check

Before writing anything clever, ask whether you have a problem at all. Two counts and a subtraction:

SELECT COUNT(*) AS rows_total,
       COUNT(DISTINCT email) AS unique_emails,
       COUNT(*) - COUNT(DISTINCT email) AS extra_copies
FROM customers

4Actually removing them

Everything so far only changed what you see. DISTINCT and rn = 1 filter a result set; the duplicate rows are still sitting in the table. Changing the stored data takes DELETE.

-- keep the lowest id per email, delete the rest
DELETE FROM customers
WHERE customer_id NOT IN (
  SELECT MIN(customer_id) FROM customers GROUP BY email
);

-- prove it worked
SELECT COUNT(*) AS rows_left, COUNT(DISTINCT email) AS unique_emails
FROM customers

MySQL will refuse this exact form with "You can't specify target table for update in FROM clause" — wrap the subquery one level deeper (SELECT id FROM (SELECT MIN(...) ...) AS t) or use a self-join. Postgres, SQLite, and SQL Server run it as written.