根据日期值删除重复

Question

我需要清理一个数据库中充满了重复的值，我想删除所有的重复和旧日期。目的：

-If串行ID有>1项，删除除最新日期以外的所有

Asset_Table (显示所有重复的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

预期结果：

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

Answer 1

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)