select * from ( select p.*, rownum rnum from ( select * from big_table ) p ) where rnum between 90 and 100
I don't think it's productive to continue the discussion whether or why it should or should not work. If anyone is still interested, they can try running this or use a table in their own schema:
select * from ( select p.*, rownum rnum from ( select * from user_tab_columns ORDER BY column_name) p ) where rnum between 21 and 30/
At 05:26 PM 1/30/2006 -0800, Bob Diss wrote:
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
