How do I keep one of many duplicate rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How do I keep one of many duplicate rows?

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2012-11-23 : 21:15:52

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

39 Posts

Posted - 2012-11-23 : 21:56:14

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.

SergioM
Posting Yak Master

170 Posts

Posted - 2012-11-24 : 11:19:28

This is exactly what I needed. Thanks!

-Sergio
I use Microsoft SQL 2008

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-24 : 12:30:11

[code]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
)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources