Delete N oldest entries in table

Question

How to delete N oldest entries. I'm limited Sybase. I need to write a stored procedure which would accept a number X and then leave only X newest entries in the table.

For example: Say ID is auto incremented. The smaller it is, the older this entry is.

ID  Text
=========
1   ASD
2   DSA
3   HJK
4   OIU

I need a procedure which would be executed like this.

execute CleanUp 2

and the result will be

ID  Text
=========
3   HJK
4   OIU

Answer 1

Note: SQL Server syntax, but should work

Delete from TableName where ID in 
    (select top N ID from TableName order by ID )

If you want N to be a parameter you will have to construct the statement string and execute it

declare @query varchar(4000)
set @query = 'Delete from TableName where ID in '
set @query = @query + '(select top ' + @N + ' ID from TableName order by ID )'
exec sp_executesql @query

Answer 2

I Like Eduardo's option best as it's the simplest solution, but since Sergej mentions it is quite slow, here's an alternative solution:

Create a stored procedure that does the following:

1. Create a temp table with the same structure as the original table.
2. Insert the top N rows into the temp table.
3. Truncate the original table.
4. Copy the rows from the temp table back to the original table.

Generally this will be much faster, especially if you have lots of rows in the table.

Answer 3

If you have a clustered index on id, it's safe to execute a delete top query.

delete top 2 from TableName;