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

By Michael Nocito, data analyst · Published August 7, 2026 By the end of this page you can check any table for duplicate rows, list every copy, and mark which one to keep, all with queries you understand. You will also know the step that comes before any query: deciding what "duplicate" means for this table, because two rows can match on everything or on one column, and those are different problems with different fixes. It is about twenty minutes. Here is what to actually do with it. On the next table you are handed, run one comparison before anything else: COUNT() against COUNT(DISTINCT key) . If the two numbers differ, the table has duplicates, and now you know before your first report does. The short version: group by the columns that define a duplicate, keep the groups where COUNT() is above one, and mark extras with ROW_NUMBER instead of deleting them. One picture carries the whole method. Rows that share a key collapse into buckets, and the buckets holding more than one row are your duplicates. The original carries a diagram here. In words: A left-to-right picture in three stages. Stage one is a column of eight row boxes. Three of them carry the same small square marker, and two others share a different marker, showing that they hold the same key value. Stage two shows the rows collapsed into five buckets: one bucket holds the three matching rows, one holds the two matching rows, and three buckets hold a single row each. The two buckets holding more than one row are outlined in a warm warning color and labelled with their counts, three and two. Stage three shows only those two flagged buckets passing through to the result. The picture shows that grouping rows by their key makes every duplicate visible as a bucket whose count is above one, while unique rows form buckets of one and drop away. The worked example is real. Every number on this page comes from a 14-row customers table I built with three duplicates seeded on purpose, and every query was run against it in SQLite before its output was pasted here. The table is small enough to check by eye, which is the point: you can confirm every result yourself. If GROUP BY itself is new, read GROUP BY and HAVING first and come back. Here is the table. Fourteen rows, and the signup system has misbehaved in three different ways. | customer_id | full_name | city | signup_date | | |---|---|---|---|---| | 101 | a**@keller.com | Ana Keller | Austin | 2026-01-04 | | 102 | be*******@mail.com | Ben Ortiz | Dallas | 2026-01-09 | | 103 | c******@mail.com | Cara Li | Austin | 2026-01-15 | | 104 | d****@mail.com | Dev Patel | Houston | 2026-01-20 | | 105 | e*****@mail.com | Ella Reyes | Dallas | 2026-02-02 | | 106 | f*****@mail.com | Finn Walsh | Austin | 2026-02-11 | | 103 | c******@mail.com | Cara Li | Austin | 2026-01-15 | | 107 | g****@mail.com | Gus Moran | Houston | 2026-02-19 | | 108 | h*****@mail.com | Hana Sato | Dallas | 2026-02-25 | | 105 | e*****@mail.com | Ella Reyes | Fort Worth | 2026-03-01 | | 109 | i*****@mail.com | Ivan Kova | Austin | 2026-03-06 | | 103 | c******@mail.com | Cara Li | Austin | 2026-01-15 | | 110 | j***@mail.com | Jo Brandt | Houston | 2026-03-14 | | 111 | be*******@mail.com | Benjamin Ortiz | Dallas | 2026-03-20 | 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; | total_rows | distinct_ids | |---|---| | 14 | 11 | 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. Run the test on the join key especially. If you are about to join on customer_id and this table has 14 rows for 11 ids, the join will multiply rows and every count downstream will be quietly wrong. Ten seconds here saves an afternoon there. 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; | customer_id | copies | |---|---| | 103 | 3 | | 105 | 2 | 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; | customer_id | full_name | city | signup_date | copies | | |---|---|---|---|---|---| | 103 | c******@mail.com | Cara Li | Austin | 2026-01-15 | 3 | 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; | customer_id | full_name | city | signup_date | | |---|---|---|---|---| | 103 | c******@mail.com | Cara Li | Austin | 2026-01-15 | | 103 | c******@mail.com | Cara Li | Austin | 2026-01-15 | | 103 | c******@mail.com | Cara Li | Austin | 2026-01-15 | | 105 | e*****@mail.com | Ella Reyes | Dallas | 2026-02-02 | | 105 | e*****@mail.com | Ella Reyes | Fort Worth | 2026-03-01 | 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 l