RSS

Detect Duplicate Rows – SQL Server !!

03 Jun

Duplicates

Following code is useful to select or get the count of duplicate rows. The table must have identity column, which will be used to identify the duplicate rows. Table in example is has ID as Identity Column and Columns which have duplicate data are ID, Col_1 and Col_2.

The main table view is as following:

01

SELECT Col_2, COUNT(*) TotalCount
FROM TableName
GROUP BY Col_2
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

The result after running the previous script is:

02

If you want to delete the duplicate rows from the previous table, try to run this script:

DELETE
FROM TableName
WHERE ID NOT IN
(SELECT MAX(ID)
FROM TableName
GROUP BY Col_1, Col_2)

 
Leave a comment

Posted by on June 3, 2013 in MS SQL Server

 

Tags: , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: