Re: ROWNUM

A copy of this was sent to "Denis Muyldermans" <denis.muyldermans_at_groupe-expert.com>
(if that email address didn't require changing) On Thu, 23 Mar 2000 15:17:55 GMT, you wrote:

>Hi,
>
>I have a big problem with "ROWNUM" when i use it in a query like this :
>"SELECT [...] WHERE ROWNUM > xxx"
>
>If xxx = 0, there is no problem. The query returns all the rows ! If xxx >
>0, the query returns no row !!!! Why?
>
>DB : Oracle 8.0.5 for Windows NT.
>
>Thanks for your help.
>
>Denis Muyldermans
>denis.muyldermans_at_groupe-expert.com
>
>
>

that is the way rownum works. It is assigned during row processing. It gets the first row -- assigns it the number one, says "is 1 > 5" -- no, goto next row -- assign the number 1, "is 1 > 5" -- no, goto next row and so on. Only when you get and keep a row will rownum be incremented.

You can:

select * from
 ( select t.*, rownum rnum from t where ..... )  where rnum > xxx
/

but in Oracle8.0 and before you cannot put an ORDER BY in the subquery.

In Oracle8i release 8.1 and up you can put an order by in the subquery.

--
http://osi.oracle.com/~tkyte/  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Mar 23 2000 - 12:08:32 CST