find duplicates using sql

This question has been asked many times, how to find duplicate values in a database table. Many times if you are loading data from another database or table you might load the data more than once or you were controlling the data integrity at front end which didn't worked as expected and now you have duplicate values in some columns.  As an example you might have a table called Customers and a field called Username, this column is suppose to be unique but now you have some duplicate usernames. If you want to count how many usernames are duplicated and how many usernames are still unique then you can use the GROUP BY and HAVING clause to find duplicate values.

SELECT username, COUNT(*) FROM customers_table
GROUP BY username HAVING COUNT(*) > 1

Now you can use the above technique to find duplicate rows in more than one column. If you want to find duplicates only where username and email address are same then we can add the email column in it.

SELECT username, email, COUNT(*) FROM customers_table
GROUP BY username, email HAVING COUNT(*) > 1

The same logic can be changed to find non duplicate rows only by changing the HAVING COUNT(*) > 1 to = 1, this will give us nonduplicated usernames.

SELECT username, COUNT(*) FROM customers_table
GROUP BY username HAVING COUNT(*) = 1

 



 4/13/2008


Add Comments
Name: *
Email: *
URL:
Comments: *
 


Advertisements

MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.

SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.

SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

 I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more features and make it easy for me to update contents on regular basis.