I can't believe that Oracle lacks an equivilent to SQL Server's TOP or MySQL's OFFSET/LIMIT.
This makes paging large data sets truly horrid, as you have to nest subselects at least three deep to have any sense of order because rownum is calculated before the ordering.
gah! MySQL is so much easier for day to day work!
Isn't that equivalent?
Re:Rownum?
jdavidb on 2003-02-13T20:33:39
Yes, but what do you do for page two, when you want 50 < ROWNUM < 100?
<g>
Re:Rownum?
TeeJay on 2003-02-13T23:17:01
exactly.This is much easier in MySQL.
We will probably move most this kind of SQL (large blocks) into Phrasebook classes, just doing an execute_query('queryname',%args) or something.
If rownum was computed after the order by then it would be very useful - shame it isn't.
btw - we have about 8 O Reilly PL/SQL and Oracle books scattered around the desk of the dev team - I don't have time to read them tho, thats why have the TOAD jockies to keep the scary bad bits of oracle hidden in nice procedures and objects and then wrapped in nice layers of perl. (although some of it would be better wrapped in concrete and dumped in the north sea).
Re:Rownum?
jdavidb on 2003-02-14T14:30:49
we have about 8 O Reilly PL/SQL and Oracle books scattered around the desk of the dev team
Which ones have you got?
:D (I'm going on 14 physical books now plus the CD bookshelf, with two more on order.) This place is starting to look like use PL/SQL; . Which isn't a bad thing, I suppose.
;) Re:Rownum?
TeeJay on 2003-02-14T14:48:28
erm.. a load with ants and millipedes, A Practical Oracle 8i, the rest are buried under peoples workings out and schema diagrams.I just have J P Harringtons, SQL Clearly explained and Halsall;s data Comms, Computer Networks, and open systems on my desk as well as the usual Perl books.
Re:Rownum?
wesgf on 2006-06-07T16:41:59
select
rownum myrownum
,stuff
from
mytable
where
myrownum>50
and myrownum<50Re:Rownum?
jdavidb on 2006-06-08T20:04:48
That's great, but it doesn't work.
Re:Rownum?
wesgf on 2006-06-08T21:08:49
select myrownum,stuff from(select (rownum) myrownum,stuff from mytable) where myrownum>50 and myrownum100
you're right, but this does.. not sure why it doesn't allow you to alias the rownum in the outer queryRe:Rownum?
wesgf on 2006-06-08T21:18:49
the only reason I think the rownum concept has merit, is if you want to have a query on a table that is ordered by something other than the primary key - and you want to find a particular value, and view the rows around it.
if there is an easy way to do that using limits and offsets let me know.. I'm trying to make a dictionary page that finds a word and prints nearby words (in mysql) - while reading up on it I found the oracle rownum stuff and decided it would be easier with that.
I guess I could do a: select count(1) from words where word.. and use that as an offset. which is probably just as easy as the rownum, glad to see I answered my own question and changed my opinion about the rownum vs limit/offset in the same post as I was writing it - I'll post it anyways to see what people think.:P Re:Rownum?
wesgf on 2006-06-08T21:20:32
select count(1) from words where word <'wordtofind'..
I presume you've already done this, since you mentioned nesting queries three levels deep.
Learn from how we do it in Perl. Got useful functionality that looks ugly, even hacking the language? Encapsulate it in a module. Abstract out the parameters you need (lower bound, upper bound, maybe table name and where clause if you want to use dynamic SQL), then stick it in a stored procedure.
Oracle PL/SQL Best Practices says:
Or, to generalize, encapsulate your queries in stored packages, procedures, and functions. Makes things reusable and, more importantly, nice and clean for you and your successors.
Re:Paging
jdavidb on 2003-02-13T20:52:42
Hmm, even better, from the beginning of the chapter:
SQL-00: Establish and follow clear rules for how to write SQL in your application.
- Never repeat a SQL statement.
- Encapsulate all SQL statements behind a procedural interface (usually a package).
- Write your code assuming that the underlying data structures will change.
- Take advantage of PL/SQL-specific enhancements for SQL.
Okay, not all of that was relevant here, but the point was, once you've written the triple-nested select, encapsulate so from now on you can use a page.next50entries routine.
