SQL to find duplicate rows
Find rows that share the same value in one or more columns.
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.
How to find duplicate rows in a table
To find duplicates you group by the column(s) that define a duplicate and keep only the groups that appear more than once.
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 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
- SQL to count rows per group
Frequently asked questions
How do I find duplicate rows in a table in SQL?
To find duplicates you group by the column(s) that define a duplicate and keep only the groups that appear more than once. 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.