Imran Azam Imran Azam - 1 year ago 96
SQL Question

SQL-Server Query_ToDelete_Records

Can anyone help me with the script which will select the latest date from the Column dtUpdated_On if Date is greater than last date and ID is less than Last ID. I know Question is not clear but try to understand.In this example i want to delete ID 1003.(I know in this example we will say... Delete from tableName where ID=1003)

ID --> dtUpdated_On
1001 --> 2009-12-11 20:08:16.857
1002 --> 2012-03-31 02:35:16.650
1003 --> 2012-09-01 00:00:00.000
1004 --> 2012-03-31 02:35:16.650

Answer Source

Assuming that by "last" you mean the row with the highest id, then you can do:

select t.*
from t join
     (select top 1 dtUpdated_On
      from t
      order by id desc
     ) last
     on t.dtUpdated_On > last.dtUpdated_On;

You can also express this in the where clause, which is simpler for deletion (in my opinion):

delete t
where t.dtUpdated_On > (select top 1 t2.dtUpdated_On
                        from t
                        order by id desc