Delete duplicate reocrds from Table based on the date sorting

  • 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:

  • 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
  • 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..:)

  • 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

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

    MJ:cool:

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

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