Finding Duplicates with SQL
Here's a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:
SQL To Find Duplicates
SELECT email, COUNT(email) AS NumOccurrences FROM users GROUP BY email HAVING ( COUNT(email) > 1 )
group by and then having a
count greater than one, we find rows with with duplicate email addresses using the above SQL.
Find Rows that Occur Once
You could also use a variant of this SQL to find rows that occur exactly once:
SELECT email FROM users GROUP BY email HAVING ( COUNT(email) = 1 )
Via Huajun Zhai's Blog. Also see How to remove duplicate rows from a table - Microsoft Knowledge base article (139444).
Like this? Follow me ↯Tweet Follow @pfreitag
Finding Duplicates with SQL was first published on October 06, 2004.
If you like reading about sql, databases, or duplicates then you might also like:
- Cheat Sheet for SQL Server
- Use Char instead of Varchar to Store UUID's
- Sphinx - Open Source SQL Full Text Search Engine
- Updated SQL Reserved Words Checker
- MySQL FULLTEXT Indexing and Searching
- SQL to Select a random row from a database table
- Insert Delayed with MySQL
- Multiple Inserts with MySQL