Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Tuesday January 28, 2003
01:58 PM

Paging query results for fun and profit

[ #10242 ]

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

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.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • 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.
    • 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 fi

      --
      J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers