Finding duplicate records with SQL

Although I don't spend a lot of time doing any type of relational database administration, occasionally I get asked to find duplicate records in tables. Here's the easiest way to do it, assuming your database supports subqueries:

select f1, f2, f3 from t1 where f1 in (
   select f1 from t1 group by f1 having count(f1) > 1
)
order by f1;

I've surmised that you're trying to find duplicates on the "f1" field. Substitute "t1" with your actual table name and "f1", "f2" and "f3" with your actual field names and include or omit as many field names you want to output in the first part of the statement.

Due to the large volume of spam, comments are disabled. If you have anything relevant to say, you can leave a , or contact me directly.

About the author

Ivan's mugshotI'm Ivan Lutrov and I'm the owner of Lutrov Interactive. I have 25 years of experience producing interactive work and I create cost effective business websites that are simple, engaging and easy to use. I practice what I preach and I say what I really think, even if it's sometimes not what you want to hear. Subscribe to the Lutrov Interactive feed via RSS and follow me on Twitter.