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


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 »

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 »

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 »