Duplicate Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Duplicate Rows

Author

Topic

easy_goer
Starting Member

21 Posts

Posted - 2013-11-04 : 02:12:54

Hello. I have a table that contains several duplicate rows. I would like to remove the duplicates except for one of them. How can I accomplish this?

For instance, a small portion of my table looks like this..

Test1 3016482 56395 3
Test2 5489108 57921 2
Test1 3016482 56395 3
Test3 8079081 89302 1
Test4 8789080 24908 2
Test1 3016482 56395 3

There are three duplicates in here and I would like to eliminate two of them.

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 02:32:27

The below will list out duplicate occurances for you


SELECT Col1,Col2,Col3,Col4
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Col1,Col2,Col3,Col4 ORDER BY Col1) AS RN,*
FROM Table
)t
WHERE RN >1

Once you're happy that it gives you desired records turn it into a delete to remove them from your table as below


DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Col1,Col2,Col3,Col4 ORDER BY Col1) AS RN
FROM Table
)t
WHERE RN >1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

easy_goer
Starting Member

21 Posts

Posted - 2013-11-04 : 11:42:13

This is great. Thank you! Exactly what I needed. I do have a follow up questions however.

I have another, but similar table that looks like the following. I'm looking for a script that will purge the lines with the lowest numbers in the last column. So, just the row with the highest number in the last column to remain.

Test1 3016482 56395 1
Test1 3016482 56395 2
Test2 5489108 57921 2
Test2 5489108 57921 3
Test3 8079081 89302 1
Test3 8079081 89302 2
Test3 8079081 89302 3

Thanks again!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-04 : 12:51:06

Do a similar thing to what Visakh posted - with one difference. Change the "ORDER BY Col1" to "ORDER BY LastColumn DESC".

Be sure to run the changed query with the select statement as he had indicated - that will show you the rows that are going to be deleted. Then run the delete statement after making similar modification.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources