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.
Syntax
SELECT email, event, COUNT(*)
FROM signups
GROUP BY email, event
HAVING COUNT(*) > 1GROUP 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, notWHERE: 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.
| id | event | signed_up | |
|---|---|---|---|
| 1 | ava@example.com | Webinar | 2026-03-02 |
| 2 | ben@example.com | Webinar | 2026-03-02 |
| 3 | ava@example.com | Webinar | 2026-03-03 |
| 4 | chloe@example.com | Workshop | 2026-03-04 |
| 5 | ben@example.com | Workshop | 2026-03-05 |
| 6 | dan@example.com | Webinar | 2026-03-05 |
| 7 | ava@example.com | Webinar | 2026-03-06 |
| 8 | eve@example.com | Workshop | 2026-03-07 |
| 9 | chloe@example.com | Workshop | 2026-03-09 |
| 10 | finn@example.com | Webinar | 2026-03-10 |
Table: signups
| id | event | signed_up | |
|---|---|---|---|
| 1 | ava@example.com | Webinar | 2026-03-02 |
| 2 | ben@example.com | Webinar | 2026-03-02 |
| 3 | ava@example.com | Webinar | 2026-03-03 |
| 4 | chloe@example.com | Workshop | 2026-03-04 |
| 5 | ben@example.com | Workshop | 2026-03-05 |
| 6 | dan@example.com | Webinar | 2026-03-05 |
| 7 | ava@example.com | Webinar | 2026-03-06 |
| 8 | eve@example.com | Workshop | 2026-03-07 |
| 9 | chloe@example.com | Workshop | 2026-03-09 |
| 10 | finn@example.com | Webinar | 2026-03-10 |
| COUNT(*) | |
|---|---|
| ava@example.com | 3 |
| ben@example.com | 2 |
| chloe@example.com | 2 |
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.
Table: signups
| id | event | signed_up | |
|---|---|---|---|
| 1 | ava@example.com | Webinar | 2026-03-02 |
| 2 | ben@example.com | Webinar | 2026-03-02 |
| 3 | ava@example.com | Webinar | 2026-03-03 |
| 4 | chloe@example.com | Workshop | 2026-03-04 |
| 5 | ben@example.com | Workshop | 2026-03-05 |
| 6 | dan@example.com | Webinar | 2026-03-05 |
| 7 | ava@example.com | Webinar | 2026-03-06 |
| 8 | eve@example.com | Workshop | 2026-03-07 |
| 9 | chloe@example.com | Workshop | 2026-03-09 |
| 10 | finn@example.com | Webinar | 2026-03-10 |
| event | COUNT(*) | |
|---|---|---|
| ava@example.com | Webinar | 3 |
| chloe@example.com | Workshop | 2 |
event, so each is alone in its group. Choosing the columns is the real decision in this query.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.
Table: signups
| id | event | signed_up | |
|---|---|---|---|
| 1 | ava@example.com | Webinar | 2026-03-02 |
| 2 | ben@example.com | Webinar | 2026-03-02 |
| 3 | ava@example.com | Webinar | 2026-03-03 |
| 4 | chloe@example.com | Workshop | 2026-03-04 |
| 5 | ben@example.com | Workshop | 2026-03-05 |
| 6 | dan@example.com | Webinar | 2026-03-05 |
| 7 | ava@example.com | Webinar | 2026-03-06 |
| 8 | eve@example.com | Workshop | 2026-03-07 |
| 9 | chloe@example.com | Workshop | 2026-03-09 |
| 10 | finn@example.com | Webinar | 2026-03-10 |
| id | event | signed_up | |
|---|---|---|---|
| 1 | ava@example.com | Webinar | 2026-03-02 |
| 3 | ava@example.com | Webinar | 2026-03-03 |
| 7 | ava@example.com | Webinar | 2026-03-06 |
| 4 | chloe@example.com | Workshop | 2026-03-04 |
| 9 | chloe@example.com | Workshop | 2026-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.
Table: signups
| id | event | signed_up | |
|---|---|---|---|
| 1 | ava@example.com | Webinar | 2026-03-02 |
| 2 | ben@example.com | Webinar | 2026-03-02 |
| 3 | ava@example.com | Webinar | 2026-03-03 |
| 4 | chloe@example.com | Workshop | 2026-03-04 |
| 5 | ben@example.com | Workshop | 2026-03-05 |
| 6 | dan@example.com | Webinar | 2026-03-05 |
| 7 | ava@example.com | Webinar | 2026-03-06 |
| 8 | eve@example.com | Workshop | 2026-03-07 |
| 9 | chloe@example.com | Workshop | 2026-03-09 |
| 10 | finn@example.com | Webinar | 2026-03-10 |
| id | event | |
|---|---|---|
| 1 | ava@example.com | Webinar |
| 1 | ava@example.com | Webinar |
| 3 | ava@example.com | Webinar |
| 3 | ava@example.com | Webinar |
| 4 | chloe@example.com | Workshop |
| 7 | ava@example.com | Webinar |
| 7 | ava@example.com | Webinar |
| 9 | chloe@example.com | Workshop |
SELECT DISTINCT and you are back to five. A window function avoids both the grouping and this trap: see the answer to exercise 2.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.
Table: signups
| id | event | signed_up | |
|---|---|---|---|
| 1 | ava@example.com | Webinar | 2026-03-02 |
| 2 | ben@example.com | Webinar | 2026-03-02 |
| 3 | ava@example.com | Webinar | 2026-03-03 |
| 4 | chloe@example.com | Workshop | 2026-03-04 |
| 5 | ben@example.com | Workshop | 2026-03-05 |
| 6 | dan@example.com | Webinar | 2026-03-05 |
| 7 | ava@example.com | Webinar | 2026-03-06 |
| 8 | eve@example.com | Workshop | 2026-03-07 |
| 9 | chloe@example.com | Workshop | 2026-03-09 |
| 10 | finn@example.com | Webinar | 2026-03-10 |
| id | event | signed_up | |
|---|---|---|---|
| 3 | ava@example.com | Webinar | 2026-03-03 |
| 7 | ava@example.com | Webinar | 2026-03-06 |
| 9 | chloe@example.com | Workshop | 2026-03-09 |
NOT IN returns the rest. Change NOT IN to IN for the clean list.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.
Table: contacts
| id | |
|---|---|
| 1 | dan@example.com |
| 2 | Dan@Example.com |
| 3 | eve@example.com |
| 4 | eve@example.com |
| 5 | finn@example.com |
| No rows |
LOWER(TRIM(email)), in the SELECT and in the GROUP BY. Exercise 4 asks for exactly that.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.
Return each date (signed_up) on which more than one sign-up arrived, and how many arrived that day.
Table: signups
| id | event | signed_up | |
|---|---|---|---|
| 1 | ava@example.com | Webinar | 2026-03-02 |
| 2 | ben@example.com | Webinar | 2026-03-02 |
| 3 | ava@example.com | Webinar | 2026-03-03 |
| 4 | chloe@example.com | Workshop | 2026-03-04 |
| 5 | ben@example.com | Workshop | 2026-03-05 |
| 6 | dan@example.com | Webinar | 2026-03-05 |
| 7 | ava@example.com | Webinar | 2026-03-06 |
| 8 | eve@example.com | Workshop | 2026-03-07 |
| 9 | chloe@example.com | Workshop | 2026-03-09 |
| 10 | finn@example.com | Webinar | 2026-03-10 |
Show the answer
SELECT signed_up, COUNT(*)
FROM signups
GROUP BY signed_up
HAVING COUNT(*) > 1| signed_up | COUNT(*) |
|---|---|
| 2026-03-02 | 2 |
| 2026-03-05 | 2 |
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.
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
| id | event | signed_up | |
|---|---|---|---|
| 1 | ava@example.com | Webinar | 2026-03-02 |
| 2 | ben@example.com | Webinar | 2026-03-02 |
| 3 | ava@example.com | Webinar | 2026-03-03 |
| 4 | chloe@example.com | Workshop | 2026-03-04 |
| 5 | ben@example.com | Workshop | 2026-03-05 |
| 6 | dan@example.com | Webinar | 2026-03-05 |
| 7 | ava@example.com | Webinar | 2026-03-06 |
| 8 | eve@example.com | Workshop | 2026-03-07 |
| 9 | chloe@example.com | Workshop | 2026-03-09 |
| 10 | finn@example.com | Webinar | 2026-03-10 |
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| id | event | |
|---|---|---|
| 1 | ava@example.com | Webinar |
| 3 | ava@example.com | Webinar |
| 7 | ava@example.com | Webinar |
| 4 | chloe@example.com | Workshop |
| 9 | chloe@example.com | Workshop |
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.
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
| id | event | signed_up | |
|---|---|---|---|
| 1 | ava@example.com | Webinar | 2026-03-02 |
| 2 | ben@example.com | Webinar | 2026-03-02 |
| 3 | ava@example.com | Webinar | 2026-03-03 |
| 4 | chloe@example.com | Workshop | 2026-03-04 |
| 5 | ben@example.com | Workshop | 2026-03-05 |
| 6 | dan@example.com | Webinar | 2026-03-05 |
| 7 | ava@example.com | Webinar | 2026-03-06 |
| 8 | eve@example.com | Workshop | 2026-03-07 |
| 9 | chloe@example.com | Workshop | 2026-03-09 |
| 10 | finn@example.com | Webinar | 2026-03-10 |
Show the answer
SELECT id, email, event
FROM signups
WHERE id IN (
SELECT MAX(id)
FROM signups
GROUP BY email, event
)| id | event | |
|---|---|---|
| 2 | ben@example.com | Webinar |
| 5 | ben@example.com | Workshop |
| 6 | dan@example.com | Webinar |
| 7 | ava@example.com | Webinar |
| 8 | eve@example.com | Workshop |
| 9 | chloe@example.com | Workshop |
| 10 | finn@example.com | Webinar |
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.
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
| id | |
|---|---|
| 1 | dan@example.com |
| 2 | Dan@Example.com |
| 3 | eve@example.com |
| 4 | eve@example.com |
| 5 | finn@example.com |
Show the answer
SELECT LOWER(TRIM(email)), COUNT(*)
FROM contacts
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1| LOWER(TRIM(email)) | COUNT(*) |
|---|---|
| dan@example.com | 2 |
| eve@example.com | 2 |
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.