SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Duplicate Rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

easy_goer
Starting Member

21 Posts

Posted - 11/04/2013 :  02:12:54  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/04/2013 :  02:32:27  Show Profile  Reply with Quote
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
Go to Top of Page

easy_goer
Starting Member

21 Posts

Posted - 11/04/2013 :  11:42:13  Show Profile  Reply with Quote
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!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 11/04/2013 :  12:51:06  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000