iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To find duplicate rows in SQL, first decide which columns make two records duplicates. Use GROUP BY with HAVING COUNT(*) > 1 to find repeated keys and their counts; use ROW_NUMBER() when you need to see each underlying row. The right query depends on whether you mean a repeated value, a combination of values, or rows matching across every relevant column.
Decide what counts as a duplicate
SQL cannot infer your data’s business rules. It treats the columns you specify as the definition of equality, so the same table can yield different duplicate groups depending on the key you choose.
- One-column key: records sharing an email address, account number, or other business identifier.
- Composite key: records matching across a combination of fields, such as first name, last name, and date of birth.
- Matching across relevant columns: rows equal in every field you intend to compare. Include all those columns in the key; exclude surrogate IDs or audit fields only if your duplicate rule explicitly ignores them.
Write down the key before querying. If equality depends on case, whitespace, collation, or engine-specific handling of NULLs, make that rule explicit too.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Find duplicate keys and count their rows
For a repeated email address, group by the email column and keep only groups containing more than one row:
#1 Best Overall
SELECT email, COUNT(*) AS row_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;For a composite key, put every defining column in both the SELECT list and GROUP BY clause:
SELECT first_name, last_name, date_of_birth, COUNT(*) AS row_count
FROM people
GROUP BY first_name, last_name, date_of_birth
HAVING COUNT(*) > 1;WHERE filters input rows before grouping; HAVING filters the groups after aggregation. For example, add a WHERE clause when you want to search only records from a particular period, and keep HAVING COUNT(*) > 1 to return repeated groups. SQL Server’s documentation also notes that GROUP BY does not order results; add ORDER BY if you need them sorted. Microsoft Learn: GROUP BY (Transact-SQL)
Return the individual rows in duplicate groups
A grouped query returns one row per duplicate group, not every source record. To label individual records, use ROW_NUMBER() over the columns that define a duplicate:
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →SELECT id, email, created_at,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at DESC, id DESC
) AS duplicate_rank
FROM customers;PARTITION BY email starts numbering again for each email address. In this example, the newest record receives rank 1, with id breaking ties on created_at. The ordering is an example retention rule, not a universal choice.
To see only the additional rows within repeated groups, put the window query in a common table expression and filter its result:
WITH ranked AS (
SELECT id, email, created_at,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at DESC, id DESC
) AS rn
FROM customers
)
SELECT *
FROM ranked
WHERE rn > 1;Replace the example columns and ordering with the actual duplicate key and review rule. Microsoft documents that ROW_NUMBER() numbers rows sequentially within each partition, beginning at 1. If the ordering columns do not uniquely determine the order within a partition, numbering can be nondeterministic; include a unique tie-breaker when the chosen row matters. Microsoft Learn: ROW_NUMBER (Transact-SQL)
Choose between GROUP BY, ROW_NUMBER, and DISTINCT
| Method | What it answers | What it returns |
|---|---|---|
GROUP BY with HAVING COUNT(*) > 1 |
Which keys repeat, and how many rows each group contains? | One result row per repeated key. |
ROW_NUMBER() |
Which individual source rows belong to repeated groups, or which rows rank after a chosen survivor? | Source rows labeled with a rank; filter in an outer query or CTE to show ranks above 1. |
SELECT DISTINCT |
What unique values should this query display? | One output row per equal combination of selected expressions; it does not identify the source records that shared those values. |
DISTINCT removes repeated projected output from the query result. It can be useful for a report that should show unique values, but it is not a source-row audit and does not explain which source records participated in repeats. PostgreSQL also offers DISTINCT ON (key_columns) to retain one row per key. Without a suitable ORDER BY, the first row is unpredictable; the DISTINCT ON expressions must match the leftmost ORDER BY expressions. PostgreSQL 18: SELECT
Account for NULLs and database-specific equality
NULL and distinct-count behavior can affect whether a query matches your intended definition. SQL Server groups NULL values in a grouping column together. MySQL’s COUNT(DISTINCT ...) counts distinct non-NULL values; its multi-expression form counts distinct combinations that do not contain NULL. That makes COUNT(DISTINCT ...) a poor substitute for counting grouped rows when NULLs matter. Microsoft Learn: GROUP BY (Transact-SQL) MySQL Reference Manual: Aggregate Function Descriptions
These documented behaviors cover PostgreSQL, SQL Server, and MySQL; they do not establish one rule for every SQL engine, data type, or collation. If case sensitivity, whitespace normalization, NULL treatment, or collation affects the result, verify the target engine’s behavior and test representative values.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Review before removing duplicates
Finding repeated rows is a read-only query. Deleting them requires a separate policy: decide which record should survive, such as the newest, a verified record, or the one with the smallest stable ID. Then make the ranking deterministic, preview the rows marked for removal, and have a recovery plan appropriate to your database.
- Define the key and survivor rule. Use the same columns in
PARTITION BYthat define duplicates, and choose an ordering that reflects which row should remain. - Add a unique tie-breaker. If the survivor depends on the ranking, use ordering values that uniquely determine the row sequence.
- Preview the candidates. Run the CTE query with
WHERE rn > 1and inspect the selected IDs before writing a deletion. - Plan recovery. Use a backup or transaction plan suitable for the database and operation.
Microsoft’s SQL Server cleanup example uses ROW_NUMBER() to mark rows after the first and notes that an index supporting the partition key and ordering columns can help performance. Its example uses ORDER BY (SELECT NULL), which does not select a survivor according to a business rule; do not copy that ordering when the choice matters. The cited deletion syntax is T-SQL-specific, so adapt and verify deletion syntax for the target database. Microsoft Learn: Remove duplicate rows from a SQL Server table by using a script
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
