How to find duplicate rows of a table in sql

December 10, 2009

This is a nice quick easy way to find duplicate rows in your database based on one field.
So say for instance id like to find all users with duplicate rows in my orders table.

The following query can achieve that.

select userid, count(*) from orders group by userid HAVING count(*) > 1

//this will fetch all the userids with duplicate rows, then you could run a delete query or whatever it is you need to do to get rid of them in a while loop of this.

About me

Hello! I'm David Heward, how are you going? I'm a Senior Devops/Build Engineer, specialising in AWS & Cloud Automation. Based in London. Strong 10+ year background in Software development. Have a read of my blog. Have a look at some of my working projects. Contact me at @davehewy or on Linkedin.