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
