Delete duplicate reocrds from Table based on the date sorting

Question

Post reply

Delete duplicate reocrds from Table based on the date sorting

AMJ-458987

SSC Eights!

Points: 879
More actions
July 6, 2011 at 3:20 am

#389905

Hi,
I have a table in the following format -
Create Table myTable(ID Varchar(20),GUID Varchar(20), ServiceDate DateTime, Description Varchar(100))
Composite Key - ID and GUID
INSERT INTO myTable ('ABC','01/02/2011','GUID1','TEST1')
INSERT INTO myTable ('ABC','01/03/2011','GUID2','TEST2')
INSERT INTO myTable ('DEF','01/04/2011','GUID3','TEST3')
INSERT INTO myTable ('GHI','01/05/2011','GUID4','TEST4')
INSERT INTO myTable ('GHI','01/06/2011','GUID5','TEST5')
INSERT INTO myTable ('JKL','01/07/2011','GUID6','TEST6')
INSERT INTO myTable ('MNO','01/08/2011','GUID7','TEST7')
INSERT INTO myTable ('PQR','01/09/2011','GUID8','TEST8')
INSERT INTO myTable ('PQR','01/10/2011','GUID9','TEST9')
INSERT INTO myTable ('STU','01/11/2011','GUID10','TEST10')
As per primary key combination there is not duplciate data in this table. But i am generating a report which shows only ID, ServiceDate and Description.
Hence the result will be duplicate data in report.
Problem - My user wants to delete the duplicate records which have similar "ID". The criterion should be keeping the records with the latest ServiceDate.
For example from the below records -
INSERT INTO myTable ('ABC','01/02/2011','GUID1','TEST1')
INSERT INTO myTable ('ABC','01/03/2011','GUID2','TEST2')
Then report should show -
ABC , 01/03/2011,GUID2,TEST2.
Till that requirement i was using this query to delete duplicate records -
WITH temptable AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS rownumber,*
FROM myTable
)
SELECT * FROM temptable WHERE rownumber > 1
Could any one please help me to filter the records based on the date range and then delete. Also please reply if any one needs more information on that.
Thanks in Advance.
MJ:cool:

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26966 More actions · Answer 1

Change

PARTITION BY ID ORDER BY ID

to

PARTITION BY ID ORDER BY ServiceDate DESC

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

amit_adarsh Ten Centuries Points: 1273 More actions · Answer 2

Hi

Create Table myTable(ID Varchar(20), ServiceDate DateTime,GUID Varchar(20), Description Varchar(100))

--Composite Key - ID and GUID

INSERT INTO myTable VALUES('ABC','01/02/2011','GUID1','TEST1')

INSERT INTO myTable VALUES ('ABC','01/03/2011','GUID2','TEST2')

INSERT INTO myTable VALUES('DEF','01/04/2011','GUID3','TEST3')

INSERT INTO myTable VALUES('GHI','01/05/2011','GUID4','TEST4')

INSERT INTO myTable VALUES('GHI','01/06/2011','GUID5','TEST5')

INSERT INTO myTable VALUES('JKL','01/07/2011','GUID6','TEST6')

INSERT INTO myTable VALUES('MNO','01/08/2011','GUID7','TEST7')

INSERT INTO myTable VALUES('PQR','01/09/2011','GUID8','TEST8')

INSERT INTO myTable VALUES('PQR','01/10/2011','GUID9','TEST9')

INSERT INTO myTable VALUES('STU','01/11/2011','GUID10','TEST10')

select * from myTable

select * from myTable bb where ID in (select id from myTable group by ID) and ServiceDate= (select MAX(ServiceDate) from myTable aa where aa.ID =bb.ID) order by ID

enjoy..:)

kramaswamy SSCoach Points: 18181 More actions · Answer 3

Mark-101232 (7/6/2011)
Change
PARTITION BY ID ORDER BY ID
to
PARTITION BY ID ORDER BY ServiceDate DESC

I'd agree with this suggestion.

Although you'll need to make one more change - You want only the first row in this case, not all the ones greater than rowNumber = 1. Switch it from

WITH temptable AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS rownumber,*

FROM myTable

)

SELECT * FROM temptable WHERE rownumber > 1

to

WITH temptable AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ServiceDate DESC) AS rownumber,*

FROM myTable

)

SELECT * FROM temptable WHERE rownumber = 1

AMJ-458987 SSC Eights! Points: 879 More actions · Answer 4

Thanks a lot.... Mark and kramaswamy........:-)

MJ:cool: