[RESOLVED] Delete statement

[usamaalam]

Hello everybody,

I need to write a delete statement to delete all the records in a table except last 100 records. I have an autogenerated ID field, so you can say delete all the records except those 100 with greatest value in ID field.

Any idea??

Thanks.

[dee-u]

If it is Access then this should be able to do it. If in SQL Server then just use DELETE FROM.

DELETE * FROM Table WHERE ID NOT IN (SELECT TOP 100 ID FROM Table ORDER BY ID DESC)