Sybase offset for pagination

pagination sybase
kospiotr picture kospiotr · Oct 13, 2011 · Viewed 14.3k times · Source

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

ozkar picture ozkar · Dec 11, 2012 
// 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

Related questions

How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Check if a user has scrolled to the bottom

I'm making a pagination system (sort of like Facebook) where the content loads when the user scrolls to the bottom. I imagine the best way to do that is to find when the user is at the bottom of the …

Read More
How to do paging in AngularJS?

I have a dataset of about 1000 items in-memory and am attempting to create a pager for this dataset, but I'm not sure on how to do this. I'm using a custom filter function to filter the results, and that works …

Read More