Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Problem with limitation of result

Re: Problem with limitation of result

From: Stephane Faroult <>
Date: Tue, 29 Jun 2004 15:01:16 +0200
Message-Id: <>

 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 <> sent: if i understand that right, i didn't really need the subquery! so i'm able to simply do:

select rownum rn, blah

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?


>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
>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.
>Stephane Faroult
>On Tue, 29 Jun 2004 13:00 , Beni Buess <[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 "<".
>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:[2]
>To unsubscribe send email to:[3]
>put 'unsubscribe' in the subject line.
>Archives are at[4]
>FAQ is at[5]
>This message has been checked for all known viruses by Messagelabs.

This message may contain legally privileged or confidential information and is therefore addressed to the named persons only. The recipient should inform the sender and delete this message, if he/she is not named as addressee.
The sender disclaims any and all liability for the integrity and punctuality of this message.
The sender has activated an automatic virus scanning by Messagelabs, but does not guarantee the virus free transmission of this message.

Please see the official ORACLE-L FAQ:[6]

To unsubscribe send email to:[7] put 'unsubscribe' in the subject line.
Archives are at[8]
FAQ is at[9]

--- Links ---
   1 javascript:parent.opencompose('','','','')
   2 modules/
   3 javascript:parent.opencompose('','','','')
   4 modules/
   5 modules/
   6 modules/
   7 javascript:parent.opencompose('','','','')
   8 modules/
   9 modules/
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue Jun 29 2004 - 07:59:43 CDT

Original text of this message