SQL to find duplicate rows
Find duplicate rows in SQL based on a single column or multiple columns — check for and count duplicate records that share the same values.
SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY count DESC;SELECT *
FROM users
WHERE email IN (
SELECT email
FROM users
GROUP BY email
HAVING COUNT(*) > 1
);The first query lists the duplicated values; this one returns the full rows behind them.
SELECT first_name, last_name, date_of_birth, COUNT(*) AS count
FROM customers
GROUP BY first_name, last_name, date_of_birth
HAVING COUNT(*) > 1
ORDER BY count DESC;When a duplicate is defined by a combination, list every column in both SELECT and GROUP BY — a row only counts as a duplicate when all of them match.
How to find duplicate rows in a table
To find duplicate rows you group by the column(s) that define a duplicate and keep only the groups that appear more than once. Group by one column for simple duplicates, or by several columns when a duplicate is defined by the combination — the same query also lets you check whether a table has any duplicate records before you clean them up.
How it works
- GROUP BY collapses rows that share the same email into one group.
- COUNT(*) counts how many rows landed in each group.
- HAVING filters groups after aggregation — WHERE cannot be used here because the count does not exist until after grouping.
- Group by multiple columns (e.g. GROUP BY first_name, last_name) when a duplicate is defined by a combination.
Tip
HAVING runs after GROUP BY, WHERE runs before — use WHERE to filter rows first, HAVING to filter the aggregated groups.
Related SQL queries
- SQL to delete duplicate rows (keep one)
- SQL to find rows not in another table
- SQL to find the second highest value
- SQL to find the nth highest value
- SQL to number and rank rows
- SQL to get the top n rows per group
Frequently asked questions
How do I find duplicate rows in a table in SQL?
To find duplicate rows you group by the column(s) that define a duplicate and keep only the groups that appear more than once. Group by one column for simple duplicates, or by several columns when a duplicate is defined by the combination — the same query also lets you check whether a table has any duplicate records before you clean them up. GROUP BY collapses rows that share the same email into one group. COUNT(*) counts how many rows landed in each group. HAVING filters groups after aggregation — WHERE cannot be used here because the count does not exist until after grouping. Group by multiple columns (e.g. GROUP BY first_name, last_name) when a duplicate is defined by a combination.
Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?
Yes — this page lists the query for each dialect, since the syntax can differ between database engines.
Can I generate this query for my own tables?
Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.