How to Find and Remove Duplicate Column Records in a Table Row (SQL Server)

by Updated August 14, 2013
Here's a couple quick SQL database tips for finding and deleting duplicate values in a SQL Server table.

To find duplicates in a Column use the following SQL:
SELECT ColName1, COUNT(*) TotalCount
FROM TableName GROUP BY ColName1 HAVING (COUNT(ColName1) > 1)
Note: Using COUNT(*) to find duplicate rows allows the query to find duplicates if ColName1 excepts NULL values.

To find duplicates in a Column and return all columns from the Table use the follow SQL:
SELECT        t1.*
FROM            TableName AS t1
WHERE        (ColumnName1 IN
            (SELECT        ColumnName1
            FROM            TableName AS t2
            GROUP BY ColumnName1
            HAVING         (COUNT(ColumnName1) > 1)))



--------------------------------------------------------------

To get Total Duplicates Use the following SQL:
SELECT COUNT(*) as TotalDuplicates FROM
(
SELECT ColName1, COUNT(*) TotalCount
FROM TableName
GROUP BY ColName1
HAVING COUNT(*) > 1
) as t

--------------------------------------------------------------

To Delete Duplicate Records or Rows use the followng SQL:
DELETE
 FROM TableName
 WHERE ID NOT IN
 (
 SELECT MAX(ID)
 FROM TableName
 GROUP BY DuplicateColumn1, DuplicateColumn2
)
Note: To use the SQL code above the table must have an identity column. The Identity Column in the example above is named "ID" and is used to identify the duplicate records. 


0
0

Add your comment

by Anonymous - Already have an account? Login now!
Your Name:  

Comment:  
Enter the text you see in the image below
What do you see?
Can't read the image? View a new one.
Your comment will appear after being approved.

Related Posts


In SQL Server 2008 Management Studio, you may want to change the default settings to allow you to edit more than the 200 rows at a time, or select more than 1000 rows, which are the default settings for SQL Server. To modify "Edit Top 200 Rows" or "Select...  more »

If you ever wanted to change a field to null in a Sql Server 2005 Management Studio result set, you're in luck because there is a quick and easy way to do it.  more »

I recently installed SQL Server 2008 database on my local computer and forgot to enable the common language runtime (CLR). Because I had forgotten to turn it on, some portions of my sites were not fuctioning properly due SQL Server not being able to...  more »

Here's a quick SQL tip on how to get records beginning with numbers only. Use the query string LIKE '[0-9]%' For example: SELECT u.UserId, u.UserName FROM dbo.aspnet_Users u WHERE u.UserName LIKE '[0-9]%' ORDER BY u.UserName Keep in mind, If you are using...  more »

UPDATE 12-16-2011: If you are using ASP.NET and your website / database is live, and you want to take the database offline, you're better off putting an App_Offline.htm file in your website main directory to first take the website down for maintance (I...  more »