Again, please reference the Oracle documentation on the subject. Also, you might find some helpful info on the ROWNUM pseudo-column at http://asktom.oracle.com, specifically http://asktom.oracle.com/pls/ask/f?p=4950:8:5024074411414130873::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:127412348064
HTH! --- Marina Hauptman <[EMAIL PROTECTED]> wrote: > As long as you are using RNUM column from the > subquery, you can collapse > the query into two levels. It works for the same > reason RNUM works in > RNUM > nnn. I did test it. > > At 11:17 AM 1/30/2006 -0800, Bob Diss wrote: > >Sorry, but this is not the case. Because of the > way > >Oracle issues ROWNUM values, you can't collapse the > >query I presented into just two levels. See the > >Oracle Database SQL Reference. On online version > can > >be found at http://tahiti.oracle.com (registration > may > >be required?). Quote: "Conditions testing for > ROWNUM > >values greater than a positive integer are always > >false." > > > >Essentially, Oracle assigns a ROWNUM to a row, and > >then increments ROWNUM, as it is output by the > WHERE > >clause. This is why you have to do things in three > >stages. If you filter ROWNUM as in this example > >("between 26 and 50") you'll discover you get > nothing. > > This is because ROWNUM is never between 26 and 50 > -- > >each row output from the WHERE clause is actually > the > >ROWNUM=1, and since that never matches the clause, > >ROWNUM is never incremented. > > > >This is why the three levels are necessary. The > >innermost query actually generates the rows in the > >sorted order. Since the ROWNUM value is assigned > >before sorting, you can't do any filtering at this > >stage on ROWNUM (the sorted records are not in > ROWNUM > >order). The middle strips off rows beyond your > page, > >and fixes the value of ROWNUM as an additional > column > >to the query. You can filter on ROWNUM here > because > >you're asking for rows <= some value. Finally, the > >outer query strips off the rows before the ones you > >want by filtering on the fixed-in-time value of > >ROWNUM. > > > >The problem with my original query comes from a > column > >ambiguity on the middle query. Oracle gets > confused > >by the "*, rownum as rnum" portion of the query. > This > >can be resolved by giving the innermost select a > table > >alias as in: > > > >select * from ( > > select x.*, rownum as rnum from ( > > select * from a_table where a_clause order by > >a_clause > > ) x where rownum <= 50 > >) where rnum >= 26 > > > >Thanks for pointing out the problem. > > > >As Marina said, where clause conditions are best > >placed in the inner query. However, Oracle can > >sometimes (often?) transfer the conditions from the > >outer query down to the inner one. Oracle calls > this > >"predicate push". > > > >--- Marina Hauptman <[EMAIL PROTECTED]> wrote: > > > > > Regarding the query, > > > The same can be accomplished with 2 levels of > > > SELECT: > > > > SELECT * FROM ( > > > > SELECT A_TABLE.*, ROWNUM AS RNUM FROM > A_TABLE > > > ORDER BY > > > >A_CLAUSE > > > > ) WHERE RNUM BETWEEN 26 AND 50 > > > > > > Also, at least on my Oracle installation I get > an > > > error when * is not > > > qualified (A_TABLE.*) in the innermost query. > > > > > > If there is a where clause it should apply to > the > > > innermost select to take > > > advantage of existing indexes. > > > > > > > > > At 12:23 PM 1/28/2006 -0800, you wrote: > > > >One common way to return a "page" of record > from > > > >Oracle is to use a nested query. For example: > > > assume > > > >you display 25 records per page, and you wish > to > > > >retrieve page 2's records, your query would > look > > > >something like this: > > > > > > > >SELECT * FROM ( > > > > SELECT * FROM ( > > > > SELECT *, ROWNUM AS RNUM FROM A_TABLE > ORDER BY > > > >A_CLAUSE > > > > ) WHERE ROWNUM <= 50 > > > >) WHERE RNUM >= 26 > > > > > > > >The inner-most SELECT retrieves the records > you're > > > >paging and sorts them in the appropriate order. > > > The > > > >middle SELECT trims off the records after the > ones > > > you > > > >want. The outer SELECT trims off the records > > > before > > > >the ones you want. > > > > > > > >This three-step query is necessary in Oracle > > > because > > > >of the way the pseudo-column ROWNUM is assigned > > > >values. > > > > > > > >- Bob > > > > > > > >--- Philip Tellis <[EMAIL PROTECTED]> > wrote: > > > > > > > > > Sometime on Jan 27, PIXpDIaC cobbled > together > > > some > > > > > glyphs to say: > > > > > > > > > > > achieve pagination to display result sets > > > queried > > > > > from Oracle in > > > > > > multiple pages. Is there a plug-in for > HTML:: > > > > > Template to achieve > > > > > > > > > > this isn't an HTML::Template problem, this > is an > > > SQL > > > > > problem. Construct > > > > > your SQL to only return one page of data at > a > > > time > > > > > given a start and > > > > > count. Not sure how to do it in Oracle, but > > > MySQL > > > > > has a non-standard > > > > > addition called LIMIT that is added to the > end > > > of > > > > > your SQL like this: > > > > > > > > > > LIMIT 31, 10 (get 10 records starting from > the > > > > > 31st) > > > > > > > > > > -- > > > > > "Idiot I may be, but tied up I ain't." > > > > > -- Gaspode the wonder dog > > > > > (Terry Pratchett, Moving > Pictures) > > > > > > > > > > > > > > > > > > > > > >------------------------------------------------------- > > > > > This SF.net email is sponsored by: Splunk > Inc. > > > Do > > > > > you grep through log files > > > > > for problems? Stop! Download the new AJAX > > > search > > > > > engine that makes > > > > > searching your log files as easy as surfing > the > > > > > web. DOWNLOAD SPLUNK! > > > > > > > > > > > >http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642 > > > > > > _______________________________________________ > > > > > Html-template-users mailing list > > > > > Html-template-users@lists.sourceforge.net > === message truncated === __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Do you grep through log files for problems? Stop! Download the new AJAX search engine that makes searching your log files as easy as surfing the web. DOWNLOAD SPLUNK! http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642 _______________________________________________ Html-template-users mailing list Html-template-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/html-template-users
