Select last 100 rows

[babeo]

Hi
I saw before someone asks question about how to select the last 100 rows in Sybase forum, but I am unable to find this question any more, instead the question only appears in Oracle and Access forums only.

Anyway, the question is the same:
- There is a 'top' command in Access, and in Oracle forum it was translated into 'row_number >= 100', so what is the translation for Sybase then. Absolutely I try to use the 'top' command will not work in Sybase.

eg: select top 100 * from mytable order by field1 desc

This will give me syntax error. Anyone knows what can be done in Sybase?
Thanks

 

[sybaseguru]

What do you mean by last 100 rows. The way data is stored in Sybase depends on the clustered index created on the table or the identity column if you have included in the table or just a heap table. In either case the unique index or identity column will allow you to select the last most recently inserted records. For example if you use the identity column and you called it "col_id" in the table do

select * from <table> where col_id >= (select max(col_id)-100 from <table>)

Otherwise you can do
set rowcount(100)
go
and choose the first or last 100 rows depending on your clustered index.

However, it is always advisable to use indexes on a table and choose records according to a certain criteria rather than just the first or last 100 rows inserted.

Hope this helps