CODEWITHSUNDEEP
sqlsql-server
Mrparkin
Mrparkin

Reputation: 65

Remove duplicate depending on Date value

I need to clean a database filled with duplicate values, I want to remove all duplicate with and old date. Aim:

-If Serial ID has > 1 entry, remove all but the latest date

Asset_Table (Shows all duplicate Serial_ID)

    Serial_ID       |    ISSI     |     Date_Added
---------------------------------------------------------
 2PND00849EWS8J1    |   3766040   |  2012-07-06 08:52:23.000
 2PND00849EWS8J1    |   3778051   |  2016-09-26 09:21:57.000

Expected Result: 

   Serial_ID        |    ISSI     |     Date_Added
---------------------------------------------------------
 2PND00849EWS8J1    |   3778051   |  2016-09-26 09:21:57.000

Upvotes: 1

Views: 105

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use row_number() function to avoid duplication or delete them 

delete d from
(
    select *,
           row_number() over(partition by Serial_ID order by Date_Added desc) rn
    from Asset_Table
) d
where d.rn > 1;

Upvotes: 4

Behnam
Behnam

Reputation: 1063

select 
     a.Serial_ID , 
     a.ISSI ,
     Date_Added 
from Asset_table  as a 
where a.Date_Added = 
     (select max(b.Date_Added) 
      from Asset_table as b  
      where b.serial = a.serial)

Upvotes: 1

Related Questions