Sybase offset for pagination

Question

Is there any simple way to implement pagination in sybase? In postgres there are limit and offset in mysql there is limit X,Y. What about sybase? There is top clausure to limit results but to achieve full pagination there is also offset needed. It is not a problem if there are a few pags, I can simply trim results on the client side, but if there are millions of rows I would like to fetch only data that I need.

Answer 1

// First row = 1000
// Last row = 1009
// Total row = 1009 - 1000 + 1 = 10
// Restriction: exec sp_dboption 'DATABASE_NAME','select into/bulkcopy','true'
select TOP 1009 *, rownum=identity(10) 
into #people
from people 
where upper(surname) like 'B%'
select * from #people where rownum >= 1000
drop table #people
// It shoulde be better SQL-ANSI-2008 (but we have to wait):
// SELECT * FROM people
// where upper(surname) like 'B%'
//    OFFSET 1000 ROWS FETCH NEXT 10 ROWS ONLY

Answer 2

I'm very late to the party but I've happened to stumble on this problem and found a better answer using TOP and START AT from sybase doc. You need to use ORDER BY for or you will have unpredictable results.

http://dcx.sybase.com/1101/en/dbusage_en11/first-order-formatting.html

SELECT TOP 2 START AT 5 * FROM Employees ORDER BY Surname DESC;