Problem solve Get help with specific problems with your technologies, process and projects.

Paging backwards through result sets

I have written a DB2 stored procedure that returns 25 rows at a time. Paging forward is very easy - get the next 25 greater than the last row of the last result set. How do you go backwards? I need the previous 25 in ascending order. I keep getting the first 25 that are less than the first row from the last result set - not the previous 25.

Thank you for mentioning the context of your SQL question, a DB2 stored procedure. Some questions are hard to answer if I don't know the context. Cynics, and people who know me, would jump in here with "Face it, Rudy, they're all hard for you." That's actually true in this case, because I haven't used DB2 in several years.

I don't think DB2 supports the TOP keyword, but I'm going to pretend it does anyway, because the queries are easier to explain that way.

Let's say the last set of rows was 950 through 975. To get the next set, run the query again, with a WHERE clause condition using 975, the value of "the last row of the last result set."

select top 25 rowvalue
  from yourtable
 where rowvalue > 975
order by rowvalue asc

This query sounds wrong but works fine. If there are more than a 25 higher rows, which ones are the "top 25"? Actually, it's the ASC order that makes it work right. If you sort all higher rows from lowest to highest, the "top 25" are actually the lowest of these. To put it another way, the next or forward set is the "lowest of the higher." Conveniently, they are in ascending order. More importantly, the TOP keyword is returning only 25 rows -- a stored procedure is not required.

To go backwards, you have to run a different query, the "highest of the lower." Also, you have to pass in 950 instead.

select top 25 rowvalue
  from yourtable
 where rowvalue < 950  
order by rowvalue desc

The DESC sequence works the same way here, only in the backwards direction. If you sort all lower rows from highest to lowest, the "top 25" are actually the highest of these. Change the sort order to ASC, and you don't get the previous 25, you get the first 25 overall.

Okay, DESC makes the query work correctly in the backward direction. Again, note that a stored procedure is not required, because the TOP keyword restricts the results to 25 rows. Not so conveniently, these rows are returned in descending order.

There's actually a surprisingly simple solution:

select rowvalue
  from ( select top 25 rowvalue
           from yourtable
          where rowvalue < 950  
         order by rowvalue desc ) 
order by rowvalue asc

As I mentioned, I don't think DB2 supports the TOP keyword, and I don't know if there's an equivalent, like LIMIT in MySQL. (If anyone does know, I'd love to hear about it.)

So I'm guessing the reason you have a stored procedure is just to use a cursor, to avoid fetching more than 25 rows. In the backwards direction, this technique is still necessary, except you'll also have to write the rows into an array or temp table, so that you can read them back out in reverse sequence, i.e. forward. Did that make sense?

For More Information

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.