How to find duplicate rows in SQL and see every copy

To find duplicate rows in SQL, group by the columns that define a duplicate and keep the groups that occur more than once: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1. Each group the query returns is one set of duplicates. To list every duplicate row with its id, use COUNT(*) OVER (PARTITION BY email), and to pick the extra copies to delete, use ROW_NUMBER().

How do I find duplicate values in one column?

GROUP BY with HAVING finds duplicate values in one column. GROUP BY puts every row with the same email into one group, COUNT(*) counts the rows in each group, and HAVING COUNT(*) > 1 keeps only the groups with more than one row:

WITH users(id, name, email) AS (
  VALUES (1, 'Ava', '[email protected]'), (2, 'Ben', '[email protected]'), (3, 'Ava', '[email protected]'),
         (4, 'Cara', '[email protected]'), (5, 'Ben B', '[email protected]'), (6, 'Ava', '[email protected]')
)
SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;

The filter goes in HAVING, not WHERE. WHERE runs before the rows are grouped, so it cannot see COUNT(*), and WHERE COUNT(*) > 1 is an error in every major database. HAVING runs after grouping and filters the groups.

Use GROUP BY <columns> HAVING COUNT(*) > 1 to find duplicates.

How do I find duplicates based on multiple columns?

To find duplicates across multiple columns, list every column that has to match in the GROUP BY, and select the same columns. Ben’s two rows share an email but not a name, so they stop counting as duplicates once name joins the key:

WITH users(id, name, email) AS (
  VALUES (1, 'Ava', '[email protected]'), (2, 'Ben', '[email protected]'), (3, 'Ava', '[email protected]'),
         (4, 'Cara', '[email protected]'), (5, 'Ben B', '[email protected]'), (6, 'Ava', '[email protected]')
)
SELECT name, email, COUNT(*) AS copies
FROM users
GROUP BY name, email
HAVING COUNT(*) > 1
ORDER BY name, email;

Choose the key columns before you write the query. “The same person signed up twice” usually means the same email, while “the same order was imported twice” means the same customer, product and timestamp. A selected column that is not in the GROUP BY raises the GROUP BY error, which is the database asking which of the copies you meant.

Why does my duplicate query return no rows?

A duplicate query returns no rows when the GROUP BY includes a column that is unique, usually the id. Every row then has its own id, so every group holds exactly one row and HAVING COUNT(*) > 1 removes them all:

WITH users(id, name, email) AS (
  VALUES (1, 'Ava', '[email protected]'), (2, 'Ben', '[email protected]'), (3, 'Ava', '[email protected]'),
         (4, 'Cara', '[email protected]'), (5, 'Ben B', '[email protected]'), (6, 'Ava', '[email protected]')
)
SELECT id, email, COUNT(*) AS copies
FROM users
GROUP BY id, email
HAVING COUNT(*) > 1
ORDER BY id;

id	email	copies

The same happens with a created-at timestamp or any column that differs between the copies. Leave those columns out of the GROUP BY, and when you need to see them, use the window function in the next section.

How do I show every duplicate row with its id?

COUNT(*) OVER (PARTITION BY email) shows every duplicate row with its id. The window function counts the rows that share each row’s email without collapsing them into one row, and the outer query keeps the rows whose count is above one:

WITH users(id, name, email) AS (
  VALUES (1, 'Ava', '[email protected]'), (2, 'Ben', '[email protected]'), (3, 'Ava', '[email protected]'),
         (4, 'Cara', '[email protected]'), (5, 'Ben B', '[email protected]'), (6, 'Ava', '[email protected]')
),
counted AS (
  SELECT id, name, email, COUNT(*) OVER (PARTITION BY email) AS copies
  FROM users
)
SELECT id, name, email, copies
FROM counted
WHERE copies > 1
ORDER BY email, id;

The filter sits in an outer query because a window function cannot appear in the WHERE of the query that computes it. The older way joins the GROUP BY result back to the table on the key columns. It returns the same rows, except that ON u.email = d.email never matches a NULL email, so duplicates of a missing value silently drop out.

Why are rows with a NULL value counted as duplicates?

GROUP BY puts every NULL in one group, so two rows with a missing email count as duplicates of each other, even though NULL = NULL is not true in a WHERE clause. When a missing value should not count, filter the NULLs out before grouping:

WITH users(id, name, email) AS (
  VALUES (1, 'Ava', '[email protected]'), (2, 'Ben', NULL), (3, 'Ava', '[email protected]'), (4, 'Cara', NULL)
)
SELECT
  email,
  COUNT(*) AS copies
FROM users
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;

Without the WHERE email IS NOT NULL, the query also returns a NULL email group with a count of 2, for Ben and Cara.

How do I find duplicates that differ only in case or spaces?

Group by a cleaned up copy of the column, LOWER(TRIM(email)), to find duplicates that differ only in capital letters or spaces. A plain GROUP BY treats [email protected] and [email protected] as different values in PostgreSQL and in the SQL engine on this page:

WITH users(id, email) AS (
  VALUES (1, '[email protected]'), (2, '[email protected] '), (3, '[email protected]'), (4, '[email protected]')
)
SELECT
  LOWER(TRIM(email)) AS email_key,
  COUNT(*) AS copies,
  COUNT(DISTINCT email) AS spellings
FROM users
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

The spellings column tells you how many different ways the same value was typed. MySQL 8.0’s default collation, utf8mb4_0900_ai_ci, already compares text without regard to case, so there a plain GROUP BY merges the first and third rows on its own.

How do I keep one row and find the extra copies?

ROW_NUMBER() numbers the rows inside each group of duplicates, so the copy numbered 1 is the one to keep and every row numbered above 1 is an extra copy. Order the window by the column that decides which copy wins, the lowest id here:

WITH users(id, name, email) AS (
  VALUES (1, 'Ava', '[email protected]'), (2, 'Ben', '[email protected]'), (3, 'Ava', '[email protected]'),
         (4, 'Cara', '[email protected]'), (5, 'Ben B', '[email protected]'), (6, 'Ava', '[email protected]')
),
numbered AS (
  SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS copy_no
  FROM users
)
SELECT id, email, copy_no
FROM numbered
WHERE copy_no > 1
ORDER BY id;

Those ids are the rows a cleanup would remove. In PostgreSQL, the same query inside a DELETE removes them from a real table:

DELETE FROM users
WHERE id IN (
  SELECT id
  FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS copy_no
    FROM users
  ) AS numbered
  WHERE copy_no > 1
);

Run the SELECT first and check the ids before you delete anything. Use ROW_NUMBER rather than RANK here: RANK gives tied rows the same number, so two copies could both come out as 1, as RANK vs DENSE_RANK vs ROW_NUMBER shows.

The PostgreSQL manual on GROUP BY and HAVING explains when each filter runs, and the PostgreSQL window functions reference defines row_number. Duplicate checks are a common warm-up in data interviews, and more of them are in the SQL interview practice guide.

Related questions

Deleting duplicate rows has its own answers, because the DELETE syntax differs between PostgreSQL, MySQL and SQL Server. The error “aggregate functions are not allowed in WHERE” comes from writing WHERE COUNT(*) > 1, and moving the condition to HAVING fixes it. “Window functions are not allowed in WHERE” comes from filtering on ROW_NUMBER() in the query that computes it.