Q
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


This was last published in July 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close