Re: ROWNUM is driving me nuts - queries suggested produced no results

From: Daniel Fink <daniel.fink_at_sun.com>
Date: Wed, 09 Jul 2003 08:51:24 -0700
Message-ID: <F001.005C414D.20030709084434@fatcity.com>

Each row that is retrieved from the database that matches the WHERE condition is placed in a result set. The sequence within the result set is the value in the pseudocolumn ROWNUM. If Oracle is using a full-table scan, the first row of the first block is the first in the result set and is assigned ROWNUM 1. If Oracle uses an index to access the data, the first entry in the index is assigned ROWNUM 1. Once a complete result set is built, the ORDER BY clause is applied. The result may be that the order of the result set and the order presented to the user are not the same. If ROWNUM is used, the numbers may be and, in fact, usually are, out of sequence.

ROWNUM and PREDICATES (the WHERE clause)

ROWNUM can be used to restrict the amount of data returned by a query, as we will see shortly. ROWNUM is assigned for each row that matches all of the applicable conditions in the predicate. For example, ROWNUM is assigned to a row only if it matches the deptno condition. One common mistake is trying to use ROWNUM to find values greater than 1. ROWNUM conditions can only be equal to 1 or less than/less than or equal to a number other than 1. The following statement will never return a row.

SELECT ename FROM emp WHERE ROWNUM = 2;

When the first row is read from the emp table, the predicate is applied. Since this is the first row, it is conditionally assigned ROWNUM of 1. However, the condition is that ROWNUM must be equal to 2. Since this condition is not met, the row is not placed in the result set. The second row is read. Since the result set is empty, this row is assigned ROWNUM of 1. Once again, the condition is not met and the row is not placed in the result set. This process repeats until all the rows are read. However, the condition never evaluates to TRUE, so no rows are placed in the result set.

Try the following

SELECT r, GENDER
FROM (SELECT ROWNUM r, GENDER

        FROM   EMP2
        WHERE  ROWNUM <= 20)

WHERE r > 10;

*In the example below, ROWNUM is evaluated by the outer query as the ROWNUM of the outer query, not the column ROWNUM in the inner query.

MaryAnn Atkinson wrote:
>
> I definitely dont fully understand ROWNUM yet,
> and you guys so far provided more info than a couple of books
> by Oracle, that I have here. For a second I thought I'm
> beginning to get it, but the queries suggested produced no results...
>
> SQL> SELECT ROWNUM, GENDER
> 2 FROM (SELECT ROWNUM, GENDER
> 3 FROM EMP2
> 4 WHERE ROWNUM <= 20)
> 5 WHERE ROWNUM > 10;
>
> no rows selected
>
> SQL> SELECT r, GENDER
> 2 FROM (SELECT ROWNUM r, GENDER
> 3 FROM EMP2
> 4 WHERE ROWNUM <= 20)
> 5 WHERE ROWNUM > 10;
>
> no rows selected
>
> SQL> SELECT r "ROWNUM", GENDER
> 2 FROM (SELECT ROWNUM r, GENDER
> 3 FROM EMP2
> 4 WHERE ROWNUM <= 20)
> 5 WHERE ROWNUM > 10;
>
> no rows selected
>
> SQL>
>
> ... so, any help is appreciated - rownum is driving me nuts...
>
> thx
> maa
>
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: MaryAnn Atkinson
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).begin:vcard
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;;;;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard Received on Wed Jul 09 2003 - 10:51:24 CDT