Sybase offset for pagination

问题:

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.

回答1:

Quoting from http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.12:

Sybase does not have a direct equivalent to Oracle's rownum but its functionality can be emulated in a lot of cases.

You can set a maximum rowcount, which will limit the number of rows returned by any particular query:

set rowcount 150

That limit will apply until it is reset:

set rowcount 0

You could select into a temporary table, then pull data from that:

set rowcount 150

select pseudo_key = identity(3),
       col1,
       col2
  into #tempA
  from masterTable
 where clause...
 order by 2,3

select col1,col2 from #tempA where pseudo_key between 100 and 150

You could optimize storage on the temp table by storing only ID columns, which you then join back to the original table for your select.

The FAQ also suggests other solutions, including cursors or Sybperl.

回答2:

// 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

回答3:

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;

回答4:

Unfortunately Sybase does not provide the ability to set a start and offset limit. The best you can achieve is to use the SET ROWCOUNT to limit the number of records returned. If you have 1,000 records and want to page by 50 entries then something like this will bring back the first page...

set rowcount 50
select * from orders

For the second page...

set rowcount 100
select * from orders

...and then you can choose not to display the first 50 from within your Java code. Obviously as you page forward you end up having to return larger and larger data sets. Your question about what to do with 1,000,000 records doesn't seem practical for a user interface that is paginated. No user searches on Google and then pages forward 1,000 times looking for stuff.

What if I have a Natural Key?

If you do have a relatively large data set and you can use a natural key on your data, this will help limit the records returned. For example if you have a list of contacts and have an interface that allows your users to select A to Z to page the people in the directory based on Surname then you can do something like...

set rowcount 50
select * from people 
where upper(surname) like 'B%'

When there are more than 50 people with a surname starting with 'B' you use the same approach as above to page forward...

set rowcount 100
select * from people 
where upper(surname) like 'B%'

... and remove the first 50 in Java code.

Following on from this example, maybe you can limit searches by date, or some other piece of data meaningful to your users requriements.

Hope this helps!

回答5:

You could try using the set ROWCOUNT twice like this:

    declare @skipRows int, @getRows int
    SELECT @skipRows=50
    SELECT @getRows=10
    set ROWCOUNT @skipRows
    SELECT caslsource_id into #caslsource_paging FROM caslsources
    set rowcount @getRows
    Select * from caslsources where caslsource_id not in (select caslsource_id from #caslsource_paging)
    DROP TABLE #caslsource_paging

This creates a temporary table of rows to skip. You will need to add your WHERE and ORER BY clauses to both the SELECTs to skip the right pages.