Search code examples
t-sqlsybase

Delete N oldest entries in table

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
Solution

  • 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