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 <marina@xxxxxxx> 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 <philip.tellis@xxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxx
> > >
>
>https://lists.sourceforge.net/lists/listinfo/html-template-users
> > >
> >
> >
> >__________________________________________________
> >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@xxxxxxxxxxxxxxxxxxxxx
>
>https://lists.sourceforge.net/lists/listinfo/html-template-users
>
>
__________________________________________________
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
| 
