Paging query results for fun and profit

jdavidb on 2003-01-28T18:58:10

Today's gem from my O'Reilly books:

You want to select records from an ordered set, but you don't want the entire set. You want to get a specific subrange like records 11 through 20. You can't use the ROWNUM pseudocolumn because conditions like "WHERE ROWNUM > 10 and ROWNUM < 20" won't work -- if you eliminate the bottom of the range using ROWNUM, you eliminate the entire range.

The solution is this interesting piece of mastery:

SELECT *
FROM (SELECT a.*, ROWNUM rnum
      FROM (SELECT *
            FROM books
            WHERE where_clause
            ORDER BY some_column) a
      WHERE ROWNUM <= upper_bound)
WHERE rnum >= lower_bound;

:D

Bill Pribyl, author of Learning Oracle PL/SQL, credits Tom Kyte with this genius, and notes that it works only on Oracle8i or later.

---

It works in plain 8 too

Thomas on 2003-01-28T19:59:13

I've just looked at one of my old documentation pages I wrote and I have this quote from 3 years ago:

To create the effect of limit function in MySQL you need to use the automatically-on-the-fly
created rownum column. You can select and have a where clause that limits the amount of rows
you want. Like: 'select id,name from table where rownum >= 1 and rownum = 10' would get the
first 10 results from the database.

Re:It works in plain 8 too

jdavidb on 2003-01-28T21:15:07

They're not quite the same. Your notes give the solution to finding records 1 to 10 (or whatever), but won't work for records 11 to 20. The difference is that this example is designed to function for a minimum rownum greater than 1. You can't put that in directly in the way you said because rownum is based on the order of results from the query, and if you specify rownum > 1 (or rownum > n for n > 1), nothing will be returned. In English, such a query would say, "Give me a result set with no first row," which isn't possible, save for the empty set. :)

The incredibly complex SQL hackery above gets around that. It's a little like saying:

$ head -20 file | tail -10

to get lines 11-20 of a file.

Re:It works in plain 8 too

Thomas on 2003-01-28T21:50:05

Ah of course, I remember the exact problem from when I worked on it now that you say it. The guy sitting next to me that day knew a lot about oracle and showed me how to do this, but I can't remember it anymore. Could it have involved a cursor?

Re:It works in plain 8 too

runrig on 2003-01-28T23:55:28

I haven't used Oracle for awhile, but from what I remember, 'where rownum > x and rownum would work ok, but not with an ORDER BY clause, and that's why you have to use your convoluted sub-query. Its a little like (when you want rows 11 to 20):

tail +11 file | head -10 | sort

when you want:

sort file | tail +11 | head -10

Re:It works in plain 8 too

runrig on 2003-01-29T00:27:29

Auugghh. My memory is not to be trusted. You're right, rownum ranges that don't start at 1 don't work.