Find and Delete Duplicate Rows in SQL

Duplicate rows are the most common data problem there is, and the two questions that follow are always the same: how do I see them, and how do I get rid of them without losing the row I want to keep? Both are short queries once you see the shape.

This page builds a small table with real duplicates in it, so every query on the page runs and you can watch rows disappear. Nothing here touches the site’s sample tables, and the playground rebuilds its data every time you open it, so you cannot break anything.

First, decide what makes a row a duplicate

The sample database has a real example to work with. Eight customers, and one country that appears twice — so if a country were supposed to have a single customer, that would be a duplicate. Whether it actually is one is a question about the business, not about SQL.

Find the duplicates: GROUP BY and HAVING

Group by the columns that should have been unique, then keep only the groups with more than one row in them:

SELECT country, COUNT(*) AS customers
FROM Customers
GROUP BY country
HAVING COUNT(*) > 1;

That is the whole idea, and it is worth being able to explain why HAVING rather than WHERE: the count does not exist until the groups have been formed, and WHERE runs before grouping. The GROUP BY vs HAVING guide goes through the ordering in detail.

Now make the definition stricter by grouping on two columns instead of one:

SELECT country, city, COUNT(*) AS customers
FROM Customers
GROUP BY country, city
HAVING COUNT(*) > 1;

Nothing comes back. The two customers who share a country are in different cities, so under the stricter definition there are no duplicates at all. Same query shape, one extra column, opposite answer — which is why the column list is the decision that matters and not an afterthought.

See the actual rows, not just the counts

The counts tell you a problem exists; to fix it you need the offending rows themselves, ids and all. Feed the grouped result back in as a filter:

SELECT customer_id, company, country, city
FROM Customers
WHERE country IN (
    SELECT country
    FROM Customers
    GROUP BY country
    HAVING COUNT(*) > 1
)
ORDER BY country, customer_id;

Every row in every duplicated group, sorted so the copies sit next to each other. This is the query to run before deleting anything — read the rows, decide which one is the keeper, and only then move on.

On a database with window functions there is a neater version that numbers the rows within each group as it goes, so the one to keep is the one numbered 1:

SELECT customer_id, company, country,
       ROW_NUMBER() OVER (PARTITION BY country ORDER BY customer_id) AS copy_number
FROM Customers
ORDER BY country, copy_number;

Anything with copy_number above 1 is a second or later row in its group. That single idea — number the rows within each group, then act on the numbers — is the basis of most real de-duplication scripts, and it lets you choose which copy survives just by changing the ORDER BY inside the OVER clause.

Delete the extras and keep one

For this part we need a table with genuine duplicate rows in it, so the block below creates one, fills it, de-duplicates it and shows the result — all in one go, because the playground starts from the original sample data every time it opens. Rows 1 and 3 are the same person, and so are rows 2 and 5:

The safe pattern is: pick the row to keep with an aggregate, then delete everything that is not it. Keeping the lowest id means keeping the earliest row:

CREATE TABLE Signups (signup_id INT PRIMARY KEY, email STRING, city STRING);
INSERT INTO Signups VALUES
  (1, 'asha@example.com', 'Pune'),
  (2, 'ben@example.com',  'London'),
  (3, 'asha@example.com', 'Pune'),
  (4, 'chen@example.com', 'Singapore'),
  (5, 'ben@example.com',  'Bristol');

DELETE FROM Signups
WHERE signup_id NOT IN (
    SELECT MIN(signup_id)
    FROM Signups
    GROUP BY email
);

SELECT * FROM Signups;

Three rows survive, one per email, and they are the earliest of each. Swap MIN for MAX to keep the newest instead. The reason this works is that the subquery produces exactly one id per group — the keepers — and NOT IN removes everything else. Delete the DELETE statement from the block and run it again to see the five rows before de-duplication.

One caveat on NOT IN worth knowing: if the subquery can produce a NULL, the whole condition becomes unknown in a real database and the DELETE removes nothing. MIN(signup_id) of a group is never NULL when the column is a key, so the query above is safe — but if you adapt it to a nullable column, use NOT EXISTS instead.

Stop it happening again

Finding and deleting duplicates is treating the symptom. The cause is almost always a missing constraint: the database was never told that the column should be unique, so it had no reason to refuse the second row.

CREATE TABLE Members (
  email  STRING PRIMARY KEY,
  joined STRING NOT NULL
);
INSERT INTO Members VALUES ('asha@example.com', '2026-01-04');
SELECT * FROM Members;

Making the email the primary key (or giving it a UNIQUE constraint) means the second row is refused at the moment it is inserted rather than quietly becoming a data-cleaning job six months later. Add a second INSERT with the same email in the playground and the engine answers with “Cannot insert record, because it already exists in primary key index” — which is exactly the error you want. The constraints guide covers which constraint to reach for.

The interview version of this question

“How do you find duplicate records in a table?” is asked constantly, and the expected answer is the GROUP BY / HAVING query. What distinguishes a good answer:

1. Ask which columns define a duplicate before writing anything.
2. Give the GROUP BY ... HAVING COUNT(*) > 1 query.
3. Mention that you would look at the rows themselves before deleting, using the IN subquery or ROW_NUMBER version.
4. Say how you would keep one copy — MIN(id) or ROW_NUMBER() = 1 — and that you would run it as a SELECT first.
5. Finish with the constraint that would have prevented it.

That is five sentences and it covers the whole problem rather than one query from it.

Practice this topic

Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:

• Only the multi-person departmentsMedium
• Headcount per departmentMedium
• Which countries do we sell to?Easy
• How many customers do we have?Easy

See all 42 exercises →

Frequently asked questions

Related reading

• GROUP BY vs HAVING: which filter runs when
• SQL constraints: UNIQUE, NOT NULL, CHECK and keys
• INSERT, UPDATE, DELETE: changing data safely
• Window functions: ROW_NUMBER and PARTITION BY
• 25 SQL interview questions and answers