Imran Azam Imran Azam - 1 year ago 75
SQL Question

SQL Server Query_ToDelete_Records

Can anyone help me with the script which will select the latest date from the column

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 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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download