Imran Azam Imran Azam - 5 months ago 13
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 the 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

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
                       )