How to Find Duplicate Rows in SQL by Michael Nocito
Learn how to detect duplicate table rows in SQL using COUNT and GROUP BY queries based on Michael Nocito's 14-row customer dataset.
Stock photo for illustration only, not from the actual event
- Compare COUNT(*) and COUNT(DISTINCT) to spot anomalies before writing code.
- The definition of a duplicate depends entirely on the columns chosen for GROUP BY.
- Use the HAVING clause to filter groups with a count strictly greater than one.
- Apply ROW_NUMBER to mark rows for keeping instead of directly deleting them.
Published by data analyst Michael Nocito, this guide takes about twenty minutes to explain how to inspect any table for duplicate rows, list every copy, and decide which entry to retain using straightforward SQL queries. The crucial step before executing any query is defining what a duplicate actually means for that specific table, since two rows might match across every column or just on a single column, presenting distinct problems requiring different fixes.
The recommended best practice before running any report or join on a new table is comparing COUNT(*) against COUNT(DISTINCT key). If the figures diverge, duplicates exist. The worked example throughout the article relies on a purpose-built SQLite customers table containing exactly fourteen rows with three seeded duplicates, keeping the dataset small enough to verify every output visually.
Stock photo for illustration only, not from the actual event
Before writing code, defining a duplicate requires careful consideration. For instance, customer 103 appears three times with identical columns, customer 105 appears twice with differing cities, and Ben Ortiz's email is tied to two different IDs. This question has no single answer because duplication is not an inherent data property, but rather a deliberate choice regarding which columns must match for two rows to represent the exact same entity.
Understanding the distinction between primary keys, natural keys, and full-row matches is vital for database hygiene. Grouping by the wrong set of columns can mask conflicting data updates or cause unintended data loss. Running a quick distinct count check prior to table joins is an essential preventive habit for data analysts to protect downstream aggregates from silent errors.
"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."
Michael Nocito
To determine if any ID appears multiple times, execute a query selecting total rows alongside distinct customer IDs, yielding fourteen rows against eleven distinct IDs to reveal a gap of three extra copies. Moving forward, grouping by those specific ID columns and filtering with HAVING COUNT(*) > 1 isolates the exact customer records responsible for the discrepancy.
Source: Dev.to
Found something wrong in this article? Report an issue with this article
Comments
Leave a Comment