Geek Answers Handbook

Sybase pagination offset

Is there an easy way to implement pagination in sybase? There is a limit in postgres and an offset in mysql is the limit of X, Y. What about sibase? There is an upper article to limit the results, but offset is also required to achieve full pagination. This is not a problem if there are several pags, I can just trim the results on the client side, but if there are millions of rows, I would like to get only the data that I need.

+4
source share
6 answers

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

Sybase does not have the direct equivalent of Oracle rownum, but its functionality can be emulated in many cases.

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

 set rowcount 150

This limit will be applied until reset:

 set rowcount 0

You can select a temporary table and then extract data from this:

 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 can optimize the storage in the temp table by saving only the identity columns, which are then joined to the source table for your selection.

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

+2
source 
 // 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
+4
source

I was very late to the party, but accidentally stumbled upon 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

CHOOSE TOP 2 START 5 * FROM EMPLOYEES BY DESC Surname;

+1
source

Unfortunately, Sybase does not provide the ability to set a trigger limit and an offset. The best you can achieve is to use SET ROWCOUNT to limit the number of records returned. If you have 1000 records and you want to create a page with 50 records, then something like this will return the first page ...

 set rowcount 50 select * from orders

For the second page ...

 set rowcount 100 select * from orders

... and then you may not display the first 50 of the Java code. Obviously, when you go forward a page, you have to return large and large data sets. Your question about what to do with 1,000,000 entries doesn't seem practical for a paginated user interface. No custom google searches and then pages forward 1,000 times looking for things.

What if I have a natural key?

If you have a relatively large data set and you can use the natural key in your data, this will help limit the returned records. For example, if you have a contact list and an interface that allows your users to choose from A to Z to place people in the directory based on their last name, you can do something like ...

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

If you have more than 50 people with a last name starting with "B", you use the same approach as above to go forward one page ...

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

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

By following this example, perhaps you can limit your search to a date or some other piece of data relevant to your users.

Hope this helps!

0
source

You can try using 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 row table for skips. You will need to add the WHERE and ORER BY clauses for both SELECTs to skip the pages you want.

0
source

Sybase SQL Anywhere example, lines per page: 10, offset: 1000.

 SELECT top 10 start at 1001 * FROM employee order by employeeid

Note. You must specify order by columns.

0
source

More articles:


All Articles