delete duplicate rows based on lastupdate date

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 2005 Forums

Transact-SQL (2005)

delete duplicate rows based on lastupdate date

Author

Topic

tp
Starting Member

5 Posts

Posted - 2009-07-23 : 19:17:44

Hi THere,

I want to delete duplicate rows, but not based on the rowid. Because I want to delete those rows which have the lower last update date.
For ex there are two dup rows for which the PK's are
122 - every other column same except last update date - 03/10/99
34 - every other column same except last update date - 03/5/2005

I want to delete the one with with 122 as id since it has the lower date than the other duplicate row.

Can anyone please suggest how this can be done?

Thanks,

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-07-24 : 00:16:16

Show us what did you try?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-07-24 : 01:11:50

Hi ,

try this.. It works even when you have more than 2 duplicate records. It just keeps the latest record and deletes the remaining records...

DECLARE @TEST TABLE ( RowID INT, RowVal VARCHAR(30), LastUpdate DATETIME)
INSERT INTO @TEST
SELECT 1,'A','03/5/2005' UNION ALL
SELECT 2,'A','03/10/2005' UNION ALL
SELECT 3,'A','04/11/2006'

--SELECT * FROM @TEST

DELETE T
FROM @TEST T
INNER JOIN ( SELECT ROW_NUMBER() OVER ( PARTITION BY RowVal ORDER BY LastUpdate DESC) AS Sno,RowID
FROM @TEST ) T1 ON T.RowID = t1.RowID AND T1.Sno <> 1

--SELECT * FROM @TEST

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-24 : 04:47:15

[Code]
Try this one too.........
DELETE t FROM @test t
LEFT JOIN (SELECT rowval,MAX(LastUpdate) AS LastUpdate FROM @test GROUP BY rowval) t1 ON T.LastUpdate = t1.LastUpdate
WHERE t1.rowval IS NULL

DELETE T FROM @test t WHERE LastUpdate NOT IN (SELECT MAX(LastUpdate) AS LastUpdate FROM @test GROUP BY rowval)

{/code]

tp
Starting Member

5 Posts

Posted - 2009-07-24 : 17:27:44

Thanks for your help. I got it working.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-25 : 00:59:22

quote:
Originally posted by tp

Thanks for your help. I got it working.

welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-25 : 01:10:23

Use this sample data for bklr's suggestion and try again

DECLARE @TEST TABLE ( RowID INT, RowVal VARCHAR(30), LastUpdate DATETIME)
INSERT INTO @TEST
SELECT 1,'A','03/5/2005' UNION ALL
SELECT 2,'A','03/10/2005' UNION ALL
SELECT 3,'A','04/11/2006' UNION ALL
SELECT 4,'A','04/11/2006'

N 56°04'39.26"
E 12°55'05.63"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-26 : 12:50:21

You beat me to it.

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources