How to Find Duplicate Rows in SQL (and Decide What Counts as One)

1. Decide what counts as a duplicate before you write anything

Before the explanation: customer 103 appears three times with every column identical. Customer 105 appears twice with two different cities. Ben Ortiz's email appears under two different ids. Which of those three are duplicates?

That question has no single answer, and that is the real first step of any duplicate hunt. "Duplicate" is not a property of the data. It is a decision you make about which columns have to match before two rows mean the same thing. There are three common answers, and each one leads to a different query and a different cleanup.

• Whole row identical. Every column matches, like customer 103. This is almost always a loading accident: the same file imported twice, a retried insert, a copy-paste. The extra copies carry no information and are safe to remove once marked.
• Same natural key, rest differs. A natural key is the column that identifies the thing in the real world, like customer_id. Customer 105 has one id and two cities, so one row is stale and one is current. Removing the wrong one destroys real information, so here the decision is which copy to keep, not just how many to remove.
• Same person, different spelling. Ben Ortiz and Benjamin Ortiz share an email but nothing a GROUP BY can match exactly. No query on this page will catch that pair as a name match. That problem is called entity resolution, deciding when two differently-written records are the same real thing, and it gets its own guide.

So the fork is: which columns define "the same"? All of them means you are hunting loading accidents. The natural key means you are hunting conflicting versions. Neither means you may be hunting people, and that is a different tool. Everything below works for the first two, and the queries only differ in what you put after GROUP BY.

2. The ten-second test on any table

Before the explanation: without looking back at the table, how would you get one number that says whether any id appears twice?

Count the rows two ways. COUNT(*) counts every row. COUNT(DISTINCT customer_id) counts how many different id values exist. If every id appears once, the two numbers match. Any gap between them is the number of extra copies.

SELECT COUNT(*)                    AS total_rows,
       COUNT(DISTINCT customer_id) AS distinct_ids
FROM customers;

Fourteen rows, eleven distinct ids, so three rows are extra copies of something. This is the query I run on every table anyone hands me, before any join and before any report, because it takes about ten seconds and it changes what I trust. It does not tell you which rows are the copies. It tells you whether the hunt is needed at all, and 14 against 11 says yes.

3. List the duplicates with GROUP BY and HAVING

Before the explanation: the test says three extra rows exist. What would you group by to find out which customers they belong to?

Group by the columns from your step-one decision, count each bucket, and keep only the buckets with more than one row. HAVING is the clause that filters groups after they are formed, which is exactly the moment the count exists.

SELECT customer_id, COUNT(*) AS copies
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY copies DESC;

Two customers, five rows between them, three of which are extras. That accounts exactly for the gap in step two: 14 rows minus 11 ids is 3, and here they are, two extra copies of 103 plus one extra of 105.

To hunt whole-row duplicates instead, put every column in the GROUP BY. Now a bucket only forms when rows match on everything.

SELECT customer_id, email, full_name, city, signup_date,
       COUNT(*) AS copies
FROM customers
GROUP BY customer_id, email, full_name, city, signup_date
HAVING COUNT(*) > 1;

Notice what changed. Customer 105 vanished from this result, because her two rows differ on city, so they never land in the same bucket. Say why the two queries disagree about 105, in your own words, before reading on. If you can explain it, you have the whole method: the GROUP BY list is your definition of duplicate, written as code.

4. See the full duplicate rows, not just the summary

Before the explanation: the summary says customer 105 has two copies, but to decide which to keep you need to see both rows side by side. The grouped result cannot show them, because grouping collapsed them. How do you get them back?

Use the summary as a shopping list. First find the ids with duplicates, then pull every row whose id is on that list. IN is the plainest way to say it.

SELECT *
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    GROUP BY customer_id
    HAVING COUNT(*) > 1
)
ORDER BY customer_id, signup_date;

Five rows, and now the two problems look as different as they are. Customer 103 is three identical rows: a loading accident, nothing to decide. Customer 105 is two versions of one person: Dallas in February, Fort Worth in March, and someone has to say which city is true. A join back to the same summary gives the identical result, and I ran both to confirm: the same five rows either way. Use whichever reads better to you; SQL joins covers the join form.

5. Mark keepers and extras with ROW_NUMBER

Before the explanation: you can now see all five duplicate rows. What single column, added to the table, would let anyone else act on them without redoing your work?

A copy number. ROW_NUMBER() is a window function, which means it computes a value for every row without collapsing anything. PARTITION BY customer_id restarts the numbering for each customer, and ORDER BY signup_date decides who gets number one. So copy number 1 is your keeper and everything above 1 is an extra, by a rule you wrote down.

SELECT customer_id, city, signup_date,
       ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY signup_date
       ) AS copy_number
FROM customers
ORDER BY customer_id, copy_number;

The duplicated customers come back numbered like this.

Every other customer simply gets copy number 1. Wrap it in a subquery and keep copy_number > 1 and you have exactly the three extras, which I ran to confirm: two extra rows for 103 and the Fort Worth row for 105.

Here is the fork on keep-versus-delete, because it is a real decision. Deleting extras makes the table clean but destroys the evidence, and if your keeper rule was wrong, the right row is gone. Marking extras keeps every row and adds a column that says which one the reports should use. Marking costs one column. Deleting costs the ability to change your mind. That is why working analysts mark first and delete rarely: filter reports to copy_number = 1 and you get every benefit of the delete with none of the risk. Notice the marking rule is visible in the query: for 105, ORDER BY signup_date made the newer Fort Worth row the extra. If the business says newest wins, flip the order to signup_date DESC and the keeper flips too. The rule is code, so it can be reviewed and argued with.

The window function family goes much further than this one trick, and window functions walks through it properly. If you want the pattern in your fingers rather than your bookmarks, type the query yourself a few times in the SQL Drill.

6. The delete pattern, stated once, with the guard rails

Sometimes the answer really is to remove rows, usually for whole-row loading accidents like customer 103. Here is the pattern, once. In SQLite every row has a hidden rowid, so keep the smallest rowid per customer and delete the rest. Other databases use ctid, a system id, or a ROW_NUMBER subquery, but the shape is the same.

DELETE FROM customers
WHERE rowid NOT IN (
    SELECT MIN(rowid)
    FROM customers
    GROUP BY customer_id
);

I ran it on the worked table, and the counts after were 11 rows and 11 distinct ids: the ten-second test now passes. Two guard rails, and they are not optional.

• Run it as a SELECT first. Change DELETE FROM customers to SELECT * FROM customers with the same WHERE, and read the rows that would die. On this table that shows exactly the three extras and nothing else. If the select surprises you, the delete would have too, except the delete does not let you look twice.
• Keep the original table. Do the delete on a copy, or write the deleted rows to a side table first. Note that this delete pattern keeps an arbitrary earliest-loaded row, not a chosen one, so for same-key conflicts like customer 105 it can silently keep the stale city. That is exactly why marking came first.

7. The full before and after

Same table, same question: which customers have duplicate rows, and which copy should stand?

Before

SELECT customer_id, COUNT(*)
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

Correct, and not enough to act on. It names 103 and 105 but shows none of their rows, states no keeper rule, and gives the next person nothing to review.

After

-- ============================================================
-- STEP 2: Which customer rows are duplicates, and which stands?
-- WHY: 14 rows but only 11 distinct customer_ids, so 3 rows
--      are extra copies. Reports must count customers once.
-- RULE: keeper = earliest signup_date per customer_id.
--      105's city conflict (Dallas vs Fort Worth) goes to the
--      data owner; nothing is deleted here.
-- ============================================================
--SELECT each customer row with its copy number:
--   ROW_NUMBER restarts at 1 for every customer_id,
--   ordered by signup_date, so copy_number 1 = keeper
SELECT customer_id, email, full_name, city, signup_date,
       ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY signup_date
       ) AS copy_number
FROM customers
ORDER BY customer_id, copy_number;

Every row is present, the keeper rule is written where a reviewer can disagree with it, the known conflict is named, and downstream queries just filter to copy_number = 1. The comment format is the one from how to comment SQL so it teaches.

Now picture running the marking query on your own biggest table, partitioned by whatever its natural key is. Which column would you put in the ORDER BY to decide the keeper, and could you defend that choice to the person who owns the data?

8. Edge cases that make duplicate hunts go wrong

Before the explanation: your duplicate query returns zero rows, but a report downstream still double-counts customers. What are the ways both of those can be true at once?

Five that each cost someone real time.

You grouped by the wrong definition. This is the zero-rows answer. Grouping by every column finds no duplicates in a table where the same id appears twice with different cities, exactly like customer 105. When the whole-row query comes back clean, run the natural-key version before declaring the table clean.

The duplicates are not in this table. A join can multiply rows even when both tables pass the ten-second test on their own keys, if you join on a column that is not unique in either table. When a report double-counts, test the join key on each side, not just the id.

Near-matches slip every exact grouping. Trailing spaces, capital letters, and spelling variants are all different values to GROUP BY. My table holds one of these on purpose: Ben Ortiz and Benjamin Ortiz. Grouping by email catches that pair, and I ran it: ben.ortiz@mail.com shows 2 copies under 2 different ids. But grouping by name never will, and true fuzzy matching is entity resolution, not a bigger GROUP BY.

NULLs group together. Two rows with a missing email land in the same bucket, because grouping treats all NULLs as one value. A bucket of blanks with a count of 40 is not 40 duplicates. It is 40 rows missing their key, which is a different finding worth reporting on its own.

Duplicates found during a migration are a symptom, not a chore. When profiling a source system before a migration turns up duplicates, the count is evidence about how the old system behaved: retried saves, merged offices, users working around a bug. Log where they came from before anyone cleans them, because the pattern predicts what else is wrong. Profiling data before a migration covers that workflow.