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
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.
(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.
Tuesday January 28, 2003
01:58 PM
Paging query results for fun and profit
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
Loading... please wait.
--Donald E. Knuth
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.
It works in plain 8 too (Score:1)
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 (Score:2)
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
Re:It works in plain 8 too (Score:1)
Re:It works in plain 8 too (Score:1)
'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):when you want:Re:It works in plain 8 too (Score:1)