SQL practice › SQL reference › Find duplicates

SQL: find duplicates in one column, in several columns, and the full rows

To find the values that appear more than once, group by the column and keep the groups with more than one row: SELECT email, COUNT(*) FROM signups GROUP BY email HAVING COUNT(*) > 1. For duplicates across several columns, list all of them in the GROUP BY.

That query tells you which values are duplicated. It doesn't show the rows themselves, and it doesn't say which copy to keep. This page goes through all three steps, and through the two ways the first query quietly gives the wrong list.

Skip to the 4 exercises ↓

Syntax

SELECT email, event, COUNT(*)
FROM signups
GROUP BY email, event
HAVING COUNT(*) > 1
GROUP BY email, event
The columns that define a duplicate. Rows that have the same values in all of them fall into one group.
COUNT(*)
How many rows are in each group: how many copies exist.
HAVING COUNT(*) > 1
Keeps the groups with more than one row. It has to be HAVING, not WHERE: the count only exists after the grouping.

The table

Ten sign-ups for two events. Ava signed up for the Webinar three times, and Chloe for the Workshop twice. Ben's email is there twice too, but for two different events.

signups
idemaileventsigned_up
1ava@example.comWebinar2026-03-02
2ben@example.comWebinar2026-03-02
3ava@example.comWebinar2026-03-03
4chloe@example.comWorkshop2026-03-04
5ben@example.comWorkshop2026-03-05
6dan@example.comWebinar2026-03-05
7ava@example.comWebinar2026-03-06
8eve@example.comWorkshop2026-03-07
9chloe@example.comWorkshop2026-03-09
10finn@example.comWebinar2026-03-10
Live queryDuplicates in one column
Table: signups
signups
idemaileventsigned_up
1ava@example.comWebinar2026-03-02
2ben@example.comWebinar2026-03-02
3ava@example.comWebinar2026-03-03
4chloe@example.comWorkshop2026-03-04
5ben@example.comWorkshop2026-03-05
6dan@example.comWebinar2026-03-05
7ava@example.comWebinar2026-03-06
8eve@example.comWorkshop2026-03-07
9chloe@example.comWorkshop2026-03-09
10finn@example.comWebinar2026-03-10
Ctrl/⌘ + Enter to run
Result · 3 rows
emailCOUNT(*)
ava@example.com3
ben@example.com2
chloe@example.com2

Duplicates across several columns

The same person signing up for two events is fine. The same person signing up twice for the same event is the duplicate. So the unit is the pair email, event, and both columns go in the GROUP BY.

Live queryDuplicates in two columns
Table: signups
signups
idemaileventsigned_up
1ava@example.comWebinar2026-03-02
2ben@example.comWebinar2026-03-02
3ava@example.comWebinar2026-03-03
4chloe@example.comWorkshop2026-03-04
5ben@example.comWorkshop2026-03-05
6dan@example.comWebinar2026-03-05
7ava@example.comWebinar2026-03-06
8eve@example.comWorkshop2026-03-07
9chloe@example.comWorkshop2026-03-09
10finn@example.comWebinar2026-03-10
Ctrl/⌘ + Enter to run
Result · 2 rows
emaileventCOUNT(*)
ava@example.comWebinar3
chloe@example.comWorkshop2

The full duplicate rows

The grouped query returns one line per duplicated value. To see the rows themselves, with their ids and dates, join the table back to that list on the same columns.

Live queryEvery row that has a duplicate
Table: signups
signups
idemaileventsigned_up
1ava@example.comWebinar2026-03-02
2ben@example.comWebinar2026-03-02
3ava@example.comWebinar2026-03-03
4chloe@example.comWorkshop2026-03-04
5ben@example.comWorkshop2026-03-05
6dan@example.comWebinar2026-03-05
7ava@example.comWebinar2026-03-06
8eve@example.comWorkshop2026-03-07
9chloe@example.comWorkshop2026-03-09
10finn@example.comWebinar2026-03-10
Ctrl/⌘ + Enter to run
Result · 5 rows
idemaileventsigned_up
1ava@example.comWebinar2026-03-02
3ava@example.comWebinar2026-03-03
7ava@example.comWebinar2026-03-06
4chloe@example.comWorkshop2026-03-04
9chloe@example.comWorkshop2026-03-09

Without GROUP BY. A self join can do it too: match each row with the other rows that have the same email and event but a different id. It has a trap of its own.

Live querySelf join: five duplicate rows come back as eight
Table: signups
signups
idemaileventsigned_up
1ava@example.comWebinar2026-03-02
2ben@example.comWebinar2026-03-02
3ava@example.comWebinar2026-03-03
4chloe@example.comWorkshop2026-03-04
5ben@example.comWorkshop2026-03-05
6dan@example.comWebinar2026-03-05
7ava@example.comWebinar2026-03-06
8eve@example.comWorkshop2026-03-07
9chloe@example.comWorkshop2026-03-09
10finn@example.comWebinar2026-03-10
Ctrl/⌘ + Enter to run
Result · 8 rows
idemailevent
1ava@example.comWebinar
1ava@example.comWebinar
3ava@example.comWebinar
3ava@example.comWebinar
4chloe@example.comWorkshop
7ava@example.comWebinar
7ava@example.comWebinar
9chloe@example.comWorkshop

Keeping one row of each

To clean up, pick one copy per group to keep, usually the first: the lowest id. Everything else is an extra copy.

Live queryThe extra copies
Table: signups
signups
idemaileventsigned_up
1ava@example.comWebinar2026-03-02
2ben@example.comWebinar2026-03-02
3ava@example.comWebinar2026-03-03
4chloe@example.comWorkshop2026-03-04
5ben@example.comWorkshop2026-03-05
6dan@example.comWebinar2026-03-05
7ava@example.comWebinar2026-03-06
8eve@example.comWorkshop2026-03-07
9chloe@example.comWorkshop2026-03-09
10finn@example.comWebinar2026-03-10
Ctrl/⌘ + Enter to run
Result · 3 rows
idemaileventsigned_up
3ava@example.comWebinar2026-03-03
7ava@example.comWebinar2026-03-06
9chloe@example.comWorkshop2026-03-09

Deleting the duplicates

Those three rows are exactly what a clean-up would remove, and the statement that removes them uses the same WHERE:

DELETE FROM signups
WHERE id NOT IN (
  SELECT MIN(id)
  FROM signups
  GROUP BY email, event
)

A DELETE removes the rows its WHERE is true for, so run the condition as a SELECT first, as above, and check the rows before you delete anything. This form works on SQLite. MySQL's manual rules it out: there a DELETE can't select, in a subquery, from the table it is deleting from, so the statement has to be written differently.

Deleting fixes today's rows. What stops the next duplicate is a UNIQUE constraint on the same columns, which makes the database refuse a second row with the same email and event.

Duplicates the query doesn't see

GROUP BY puts two values in the same group only when they are equal. Two addresses that look the same to you may not be equal to the database.

Live queryNo duplicates found. Look at the table
Table: contacts
contacts
idemail
1dan@example.com
2Dan@Example.com
3eve@example.com
4eve@example.com
5finn@example.com
Ctrl/⌘ + Enter to run
Result · 0 rows
No rows

Practice: 4 exercises

Write the query in the box and press Run. Your result is compared with the right one, on these tables and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.

Exercise 1Days with more than one sign-up

Return each date (signed_up) on which more than one sign-up arrived, and how many arrived that day.

Table: signups
signups
idemaileventsigned_up
1ava@example.comWebinar2026-03-02
2ben@example.comWebinar2026-03-02
3ava@example.comWebinar2026-03-03
4chloe@example.comWorkshop2026-03-04
5ben@example.comWorkshop2026-03-05
6dan@example.comWebinar2026-03-05
7ava@example.comWebinar2026-03-06
8eve@example.comWorkshop2026-03-07
9chloe@example.comWorkshop2026-03-09
10finn@example.comWebinar2026-03-10
Ctrl/⌘ + Enter to run
Show the answer
SELECT signed_up, COUNT(*)
FROM signups
GROUP BY signed_up
HAVING COUNT(*) > 1
Result · 2 rows
signed_upCOUNT(*)
2026-03-022
2026-03-052

The same pattern on another column: group by the date, count the rows of each group, and keep the groups with more than one. Two sign-ups arrived on March 2 and two on March 5.

Exercise 2Every row that is duplicated

Return the id, email and event of every sign-up that has at least one duplicate: another row with the same email and the same event.

Table: signups
signups
idemaileventsigned_up
1ava@example.comWebinar2026-03-02
2ben@example.comWebinar2026-03-02
3ava@example.comWebinar2026-03-03
4chloe@example.comWorkshop2026-03-04
5ben@example.comWorkshop2026-03-05
6dan@example.comWebinar2026-03-05
7ava@example.comWebinar2026-03-06
8eve@example.comWorkshop2026-03-07
9chloe@example.comWorkshop2026-03-09
10finn@example.comWebinar2026-03-10
Ctrl/⌘ + Enter to run
Show the answer
SELECT id, email, event
FROM (
  SELECT id, email, event,
         COUNT(*) OVER (PARTITION BY email, event) AS copies
  FROM signups
) counted
WHERE copies > 1
Result · 5 rows
idemailevent
1ava@example.comWebinar
3ava@example.comWebinar
7ava@example.comWebinar
4chloe@example.comWorkshop
9chloe@example.comWorkshop

COUNT(*) OVER (PARTITION BY email, event) writes next to every row how many rows share its email and event, without folding them into groups. The outer query keeps the rows where that number is above 1. No GROUP BY, no join. The join back to the grouped query, shown above, returns the same five rows.

Exercise 3Keep the latest copy

Return a clean list with one row per email and event. When a sign-up is duplicated, keep only its latest copy: the one with the highest id. Return the id, email and event.

Table: signups
signups
idemaileventsigned_up
1ava@example.comWebinar2026-03-02
2ben@example.comWebinar2026-03-02
3ava@example.comWebinar2026-03-03
4chloe@example.comWorkshop2026-03-04
5ben@example.comWorkshop2026-03-05
6dan@example.comWebinar2026-03-05
7ava@example.comWebinar2026-03-06
8eve@example.comWorkshop2026-03-07
9chloe@example.comWorkshop2026-03-09
10finn@example.comWebinar2026-03-10
Ctrl/⌘ + Enter to run
Show the answer
SELECT id, email, event
FROM signups
WHERE id IN (
  SELECT MAX(id)
  FROM signups
  GROUP BY email, event
)
Result · 7 rows
idemailevent
2ben@example.comWebinar
5ben@example.comWorkshop
6dan@example.comWebinar
7ava@example.comWebinar
8eve@example.comWorkshop
9chloe@example.comWorkshop
10finn@example.comWebinar

The subquery returns the highest id of every email and event pair, and the outer query keeps those rows: seven of the ten. Ava's Webinar sign-up is kept as id 7 and Chloe's Workshop one as id 9. SELECT MAX(id), email, event FROM signups GROUP BY email, event is shorter and returns the same rows; the subquery form also works when you need columns that aren't in the GROUP BY, such as the date.

Exercise 4Duplicates hidden by capitals and spaces

In contacts the same address was typed in different ways. Treat addresses as equal when they differ only in capital letters or in spaces at the start or the end. Return each address that then appears more than once, in lower case and without those spaces, and how many rows hold it.

Table: contacts
contacts
idemail
1dan@example.com
2Dan@Example.com
3eve@example.com
4eve@example.com
5finn@example.com
Ctrl/⌘ + Enter to run
Show the answer
SELECT LOWER(TRIM(email)), COUNT(*)
FROM contacts
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
Result · 2 rows
LOWER(TRIM(email))COUNT(*)
dan@example.com2
eve@example.com2

TRIM removes the spaces at both ends and LOWER the capital letters, so the three ways of typing an address become one value. Grouping by that value finds two duplicated addresses, each held by two rows. Grouping by email itself finds none.

Questions people ask

How do I find duplicates in one column?

SELECT email, COUNT(*) FROM signups GROUP BY email HAVING COUNT(*) > 1. Put your column in place of email. The result has one line per duplicated value, with the number of copies.

How do I find duplicates in multiple columns?

List every column that defines the duplicate in both the SELECT and the GROUP BY: SELECT email, event, COUNT(*) FROM signups GROUP BY email, event HAVING COUNT(*) > 1. Two rows are then duplicates only when they agree on all of those columns.

How do I find rows that are duplicated in every column?

Group by every column of the table. If the table has an id that is different on every row, no two rows are equal in every column, so leave the id out and group by the rest: that is the question the page's queries answer.

How do I find duplicates without GROUP BY?

With a window function: COUNT(*) OVER (PARTITION BY email, event) puts the number of copies next to every row, and an outer query keeps the rows where it is above 1 (the answer to exercise 2). A self join on the same columns with a.id <> b.id works too, but needs DISTINCT, as the live query above shows.

Do NULLs count as duplicates?

In a GROUP BY, yes: SQLite's documentation says NULL values are considered equal for grouping, so all the rows with no email fall into one group and show up as one "duplicate". If a missing value isn't a duplicate for you, add WHERE email IS NOT NULL before the GROUP BY.

Keep going

Ten questions on a real database, from SELECT to JOINs and subqueries. Free, no sign-up.

Related

SQL interview questions, with answersSQL GROUP BY, with live queriesSQL WHERE vs HAVING, with live queriesRANK vs DENSE_RANK vs ROW_NUMBER, side by sideSQL top N per group, with live queriesSQL skills testSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: SQLite documentation: SELECT (GROUP BY, HAVING), SQLite documentation: DELETE, SQLite documentation: datatypes (collating sequences), SQLite documentation: CREATE TABLE (UNIQUE constraints), SQLite documentation: window functions, MySQL reference manual: DELETE. Last checked November 2026.