Duplicate rows creep into almost every real database: a form submitted twice, a CSV imported two times, a join that fans out. The fix depends on what you want. To hide duplicates in a result, use SELECT DISTINCT or GROUP BY. To delete duplicates from the table, keep one row per group and remove the rest, usually with ROW_NUMBER() or MIN(id).
Jump to: sample data · find duplicates · hide with DISTINCT · delete duplicates · prevent them · mistakes · FAQ
Sample data
Every query below was run on SQLite 3.45 and the output is copied from the real result. Ana appears three times with identical data, and Ben appears twice with two different cities.
CREATE TABLE contacts (id INTEGER PRIMARY KEY, name TEXT, email TEXT, city TEXT);
INSERT INTO contacts VALUES
(1, 'Ana', '[email protected]', 'Austin'),
(2, 'Ben', '[email protected]', 'Boston'),
(3, 'Ana', '[email protected]', 'Austin'),
(4, 'Chloe', '[email protected]', 'Chicago'),
(5, 'Ben', '[email protected]', 'Denver'),
(6, 'Ana', '[email protected]', 'Austin'),
(7, 'Dev', '[email protected]', 'Dallas');The id column is a unique row identifier. Even "duplicate" rows have different ids, which is what makes deleting them possible.
Step 1: Find duplicates
Always look before you delete. Decide which column (or columns) defines a duplicate, then group by it and keep groups with more than one row:
SELECT email, COUNT(*) AS copies
FROM contacts
GROUP BY email
HAVING COUNT(*) > 1;email | copies
[email protected] | 3
[email protected] | 2Two emails are duplicated. To see the individual extra rows (everything except the first copy), number the rows in each group with ROW_NUMBER():
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM contacts
) AS t
WHERE rn > 1;id | name | email | city | rn
3 | Ana | [email protected] | Austin | 2
6 | Ana | [email protected] | Austin | 3
5 | Ben | [email protected] | Denver | 2PARTITION BY email restarts the numbering for each email, and ORDER BY id makes the oldest row number 1. Rows 3, 5 and 6 are the extras. This query is your safety preview: whatever it lists is exactly what the delete queries below will remove.
Notice that Ben's two rows have the same email but different cities. Whether that is a duplicate is a business decision, not a SQL one. Here we treat the email as the identity of a contact. If you define a duplicate as "every column matches", group by all of them: GROUP BY name, email, city.
Remove duplicates from a query result
If you only need clean output and do not want to change the table, you have three options.
1. SELECT DISTINCT
SELECT DISTINCT email FROM contacts;email
[email protected]
[email protected]
[email protected]
[email protected]DISTINCT removes rows that are identical across every selected column. With one column selected, that is four unique emails. Add more columns and the rule gets stricter:
SELECT DISTINCT name, email, city FROM contacts;name | email | city
Ana | [email protected] | Austin
Ben | [email protected] | Boston
Chloe | [email protected] | Chicago
Ben | [email protected] | Denver
Dev | [email protected] | DallasAna's three copies collapse into one, but Ben stays twice because his cities differ. This is the most common surprise with DISTINCT: it cannot remove a duplicate if you also select a column that differs. See how to use SELECT DISTINCT for more.
2. GROUP BY with MIN or MAX
When you want one full row per email, group by the email and pick values with an aggregate:
SELECT MIN(id) AS id, name, email
FROM contacts
GROUP BY email
ORDER BY id;id | name | email
1 | Ana | [email protected]
2 | Ben | [email protected]
4 | Chloe | [email protected]
7 | Dev | [email protected]One row per email, keeping the lowest id. Note that SQLite and MySQL (in permissive mode) let you select name without aggregating it. PostgreSQL and SQL Server do not, so wrap it in MIN(name) there. See GROUP BY.
3. ROW_NUMBER() to keep the newest (or oldest) row
This is the most flexible option because you choose which copy survives. Order by id DESC to keep the most recent:
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) AS rn
FROM contacts
) AS t
WHERE rn = 1
ORDER BY id;id | name | email | city | rn
4 | Chloe | [email protected] | Chicago | 1
5 | Ben | [email protected] | Denver | 1
6 | Ana | [email protected] | Austin | 1
7 | Dev | [email protected] | Dallas | 1Now Ben's Denver row wins, because it has the higher id. Change the ORDER BY to a timestamp such as updated_at DESC to keep the most recently updated record. Window functions work in SQLite 3.25+, PostgreSQL, MySQL 8+, SQL Server and Oracle.
Delete duplicate rows permanently
Back up first. A DELETE cannot be undone unless you run it inside a transaction you can roll back. Run BEGIN;, execute the delete, check the result, then COMMIT; or ROLLBACK;. The previews in the section above show what will go.
Method 1: Delete everything except MIN(id)
The simplest approach, and it works in every database that lets a subquery read the table being deleted from (SQLite, PostgreSQL, SQL Server). The subquery finds the row to keep in each group; the delete removes the rest.
DELETE FROM contacts
WHERE id NOT IN (
SELECT MIN(id) FROM contacts GROUP BY email
);
SELECT * FROM contacts ORDER BY id;id | name | email | city
1 | Ana | [email protected] | Austin
2 | Ben | [email protected] | Boston
4 | Chloe | [email protected] | Chicago
7 | Dev | [email protected] | DallasRows 3, 5 and 6 are gone and every email appears once, with the oldest copy kept. Use MAX(id) to keep the newest instead. MIN(id) is never NULL for a group, so the NOT IN NULL trap does not bite here, but it would if id could be null.
MySQL note: MySQL refuses to delete from a table while selecting from the same table in a subquery (error 1093). Wrap the subquery in a derived table, NOT IN (SELECT id FROM (SELECT MIN(id) AS id FROM contacts GROUP BY email) AS keep), or use the self-join in method 3.
Method 2: Delete with ROW_NUMBER()
Use this when "which copy to keep" needs a smarter rule than lowest id. The inner query labels every extra row with rn > 1, and the delete removes those ids:
DELETE FROM contacts
WHERE id IN (
SELECT id FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM contacts
) AS t
WHERE rn > 1
);The result is identical to method 1 (ids 1, 2, 4 and 7 remain). In SQL Server you can shorten this by deleting straight from a CTE:
WITH numbered AS (
SELECT ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM contacts
)
DELETE FROM numbered WHERE rn > 1; -- SQL Server onlyPostgreSQL and MySQL cannot delete through a CTE like that, so use the IN (...) form there.
Method 3: Delete with a correlated EXISTS (or self-join)
"Delete a row if an older row with the same email exists":
DELETE FROM contacts
WHERE EXISTS (
SELECT 1 FROM contacts c2
WHERE c2.email = contacts.email
AND c2.id < contacts.id
);Again ids 1, 2, 4 and 7 remain. In MySQL the equivalent self-join is DELETE c1 FROM contacts c1 JOIN contacts c2 ON c1.email = c2.email AND c1.id > c2.id;. Learn how this pattern works in the EXISTS section of the subquery guide.
Method 4: Rows that are identical in every column and have no id
If your table has no unique id, you need something else to tell the copies apart. Most databases expose a hidden row identifier: rowid in SQLite, ctid in PostgreSQL, and ROWID in Oracle. Here we treat a duplicate as all of name, email and city matching:
DELETE FROM contacts
WHERE rowid NOT IN (
SELECT MIN(rowid) FROM contacts GROUP BY name, email, city
);
SELECT * FROM contacts ORDER BY id;id | name | email | city
1 | Ana | [email protected] | Austin
2 | Ben | [email protected] | Boston
4 | Chloe | [email protected] | Chicago
5 | Ben | [email protected] | Denver
7 | Dev | [email protected] | DallasOnly the two exact copies of Ana were removed. Both of Ben's rows survive because their cities differ. In PostgreSQL swap rowid for ctid. In SQL Server, use the ROW_NUMBER() CTE delete from method 2 with PARTITION BY name, email, city, since there is no row identifier.
Method 5: Rebuild the table
For very large tables, deleting millions of rows is slow and bloats the table. It can be faster to copy the good rows into a new table, check it, and swap the names:
CREATE TABLE contacts_clean AS
SELECT MIN(id) AS id, name, email, city
FROM contacts
GROUP BY email;Our check returned ids 1, 2, 4 and 7, the same clean set. Then drop the old table and rename the new one (ALTER TABLE contacts_clean RENAME TO contacts), and recreate any indexes and constraints, which CREATE TABLE AS does not copy.
Prevent duplicates from coming back
Cleaning is a one-off. A constraint keeps the table clean. After removing the duplicates, add a unique index on the column that defines identity:
CREATE UNIQUE INDEX ux_contacts_email ON contacts (email);I ran this on the original table before cleaning it, and SQLite refused with UNIQUE constraint failed: contacts.email. That is the good outcome: the database will now reject a second row with the same email instead of silently storing it. (Create the index after you de-duplicate, or it will fail like this.) For one-off imports, you can also write INSERT ... ON CONFLICT DO NOTHING (PostgreSQL, SQLite) or INSERT IGNORE (MySQL). Read more about creating indexes.
Common mistakes
- Deleting without previewing. Run the matching
SELECTfirst, and test inside a transaction. - Using
DISTINCTand expecting it to fix the table.DISTINCTonly changes the query result. The duplicates are still stored. - Selecting an extra column that breaks DISTINCT. A column like
idorcreated_atis different on every row, so nothing is removed. Select only the columns that define a duplicate. - Grouping by the wrong columns. Grouping by
emailmerged both Bens; grouping byname, email, citykept them apart. Pick the definition deliberately. - Forgetting
NULL.DISTINCTandGROUP BYtreat allNULLs as one group, so rows with a missing email collapse into a single row. Filter them out or handle them with COALESCE first. - Case and whitespace differences.
[email protected]and[email protected]are different strings. Compare onLOWER(TRIM(email))when cleaning real-world data. - Not adding a constraint afterwards. Without a unique index, the duplicates will return on the next import.
FAQ
How do I remove duplicates in SQL?
To hide them in a query, use SELECT DISTINCT or GROUP BY. To delete them from the table, keep one row per group (for example MIN(id)) and delete the rest with DELETE ... WHERE id NOT IN (...) or a ROW_NUMBER() subquery.
How do I find duplicate rows in SQL?
Group by the columns that define a duplicate and filter with HAVING COUNT(*) > 1. Use ROW_NUMBER() OVER (PARTITION BY ...) to list the individual extra rows.
What is the difference between DISTINCT and deleting duplicates?
DISTINCT removes duplicates from what a query returns and leaves the table untouched. A DELETE removes the rows from the table permanently.
How do I delete duplicates but keep one row?
Choose the row to keep with MIN(id), MAX(id) or ROW_NUMBER() ordered by a date, then delete every row that is not the chosen one, as shown in methods 1 and 2.
How do I delete duplicates with no unique id?
Use the database's hidden row identifier (rowid in SQLite, ctid in PostgreSQL), or in SQL Server delete through a CTE using ROW_NUMBER() partitioned by every column.
Why does DISTINCT still show duplicates?
Because at least one selected column differs between the rows. DISTINCT compares the whole row, so select only the columns that define a duplicate.
Practice
Try the free interactive DISTINCT lesson and the GROUP BY lesson in your browser, then work through the SQL practice problems. Duplicate removal is also a favourite in SQL coding interviews. Keep the SQL cheat sheet handy, and see COUNT DISTINCT if you only need to count unique values.