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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.