| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problem with limitation of result
Beni,
check the Oracle SQL Reference manual for the feature called "Top-N
reporting".
that's what Stephane is referring to; it is pretty well documented.
Kind regards,
herzliche gruesse,
cordialement,
venlig hilsen,
ciao,
met vriendelijke groet,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Stephane Faroult
Sent: Tuesday, June 29, 2004 14:01
To: oracle-l_at_freelists.org
Subject: Re: Problem with limitation of result
You need no subquery for N=0, you need one for N>0 (and in that case you need ROWNUM as rn to be in the subquery). Regards,
Stephane Faroult
On Tue, 29 Jun 2004 14:56 , Beni Buess <beni.buess_at_swisslog.com> sent: if i understand that right, i didn't really need the subquery! so i'm able to simply do:
select rownum rn, blah
from
where....
and rn BETWEEN N AND P
i tested that and got exactly the same result as i'm using a subquery. it seems to me that there is no difference since the ROWNUM is stamped to rows at the time they get out. right?
regards
beni
>
>Be also aware that if you want to really keep the work done under control,
>you should actually put a limit on *both* queries. Betting on the
>intelligence of the optimiser is always a gamble. If you want to return
>rowsN to P, you should write :
>
>select *
>
>from (select rownum rn, blah
>
>from
>
>where ...
>
>and rownum <= P) <=== Note
>
>where rn between N and P
>
>Otherwise you have a risk that your inner query returns your thousands of
>rows (which it will do, in the end) to only display a few ones.
>
>Don't complain about your English, you haven't seen my German.
>
>Regards,
>
>Stephane Faroult
>
>On Tue, 29 Jun 2004 13:00 , Beni Buess <beni.buess_at_swisslog.com[1]> sent:
>
>thank you! it works now.
>i need the between, because i need sometimes something like "BETWEEN 10
>AND 20" so i could not do this using "<".
>but:
>i thought, that i've no control over the rowid given by oracle to every
>row, so i need the inline view to force new rowid's beginning with 1 (or
>0) up to the amount of records fetched by the query, because i need this
>only for limiting the result because my webapp would not be amused
>getting thousends of records. and if i would not do this using a
>subquery, i would just get the records with a rowid - given at the time
>the record was inserted - which is between 0 and 10 for example. and
>this would not really do what i want.
>
>am i right?
>
>
>i hope you've understood that, my english is not the best and it was a
>bit difficult to explain.
>
>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com[2]
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org[3]
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/[4]
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html[5]
>-----------------------------------------------------------------
>
>____________________________________________________________
>This message has been checked for all known viruses by Messagelabs.
-- Archives are at http://www.freelists.org/archives/oracle-l/[8] FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html[9] ----------------------------------------------------------------- --- Links --- 1 javascript:parent.opencompose('beni.buess_at_swisslog.com','','','') 2 modules/refer.pl?redirect=http%3A%2F%2Fwww.orafaq.com 3 javascript:parent.opencompose('oracle-l-request_at_freelists.org','','','') 4 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Farchives%2Foracle -l%2F 5 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fhelp%2Ffom-serve% 2Fcache%2F1.html 6 modules/refer.pl?redirect=http%3A%2F%2Fwww.orafaq.com 7 javascript:parent.opencompose('oracle-l-request_at_freelists.org','','','') 8 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Farchives%2Foracle -l%2F 9 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fhelp%2Ffom-serve% 2Fcache%2F1.html ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- -- Binary/unsupported file stripped by Ecartis -- -- Type: text/x-vcard -- File: Lex de Haan.vcf ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Jun 29 2004 - 08:14:50 CDT
 |
 |