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)
 How do I keep one of many duplicate rows?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

163 Posts

Posted - 11/23/2012 :  21:15:52  Show Profile  Reply with Quote
I have a table in my database which has many duplicate rows. I'd like to keep one of the rows in that list and delete the remainder. How do I do this? When I want to eliminate all duplicates, I use the following code. I just can't think of how to modify it to work here.


DELETE FROM MyDB.dbo.Table
WHERE UPC IN	(
		SELECT UPC
		FROM MyDB.dbo.Table
		WHERE LEN(UPC)>7
		GROUP BY UPC
		HAVING COUNT(UPC) > 1
				)


-Sergio
I use Microsoft SQL 2008

Elizabeth B. Darcy
Starting Member

United Kingdom
39 Posts

Posted - 11/23/2012 :  21:56:14  Show Profile  Reply with Quote
I have often used the ROW_NUMBER function to do this. In the code below, the red UPC indicates the column(s) that you use to determine uniqueness. It does not necessarily have to be just one column, you can list any or all columns there depending on your rules for determining whether something is duplicate or not.

The "ORDER BY UPC" is an ordering that I picked randomly for you. The row that comes first in the ordering scheme will be retained, all others will be deleted. You can change that as you wish - for example, if you had a datestamp column in your table, and you wanted to retain the latest row for each UPC based on that datestamp, you would change the order by clause to "ORDER BY datestamp DESC":
;WITH cte AS
(
	SELECT
		ROW_NUMBER() OVER ( PARTITION BY UPC ORDER BY UPC) AS ROWNUM
	FROM 
		MyDB.dbo.Table
) DELETE FROM cte WHERE ROWNUM > 1;



________________________________________
-- Yes, I am indeed a fictional character.
Go to Top of Page

SergioM
Posting Yak Master

163 Posts

Posted - 11/24/2012 :  11:19:28  Show Profile  Reply with Quote
This is exactly what I needed. Thanks!

-Sergio
I use Microsoft SQL 2008
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/24/2012 :  12:30:11  Show Profile  Reply with Quote
Delete T 
FROM MyDB.dbo.Table T
Where Exists 
(
	Select *
	From MyDB.dbo.Table TBL
	Where TBL.UPC = T.UPC
         Group by TBL.UPC
	Having Count(*) > 1
	And Min(TBL.PrimaryKey) = T.PrimaryKey
 )

Edited by - sodeep on 11/24/2012 12:33:01
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.06 seconds. Powered By: Snitz Forums 2000