limiting results in sybase ASE between a particular range

Question

I have a search screen which allows the user to search information and it populates a grid. Some of the search results are returning a huge amount of data. I am trying to create a paging grid so that I only bring from the store procedure 10 or 20 results at a time. (I already have a paging grid in the UI)

I am trying to do something like this:

select * from wl_eval limit 1, 20

The query above will return only the first 20 records. How would I be able to accomplish that in Sybase ASE? Unfortunately, for my client project we are using Sybase. I know that in other database engines we could have used the query I mention above. I also know we can use SET ROW COUNT 20 but this wont work if I want a particular range, say from 30 to 50.

Any thoughts?

Answer 1

Add TOP numberofrecords you want to return

DECLARE @intStartRow int;
DECLARE @intEndRow int;

SET @intStartRow = (@intPage -1) * @intPageSize + 1;
SET @intEndRow = @intPage * @intPageSize;    

 WITH wl_eval AS
(SELECT field, 
 ROW_NUMBER() OVER(ORDER BY intID DESC) as intRow, 
 COUNT(intID) OVER() AS intTotalHits 
     FROM tblBlog)
 SELECT field, intTotalHits FROM wl
 WHERE intRow BETWEEN @intStartRow AND @intEndRow