| 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: ROWNUM is driving me nuts - queries suggested produced no res
Think of it this way: when Oracle builds a result set, it goes into the table and tries to "qualify" the records in the table by evaluating the "where" clause; if the record does not qualify, the record will not appear in the result set, if the record does qualify, then the record will be returned in the result set. Once a record is qualified and is eligible to be included in the result set, Oracle assigns it a ROWNUM value, which starts at 1 and is contiguous.
In your several examples below, the result set from the subquery is irrelevant because your enclosing query has "ROWNUM > 10", which is applied to the result set arising from your enclosing query. In a previous email, someone (who I don't remember) explained that you can't select a set of records from a result set unless your criteria for the ROWNUM starts-at and includes ROWNUM 1. (A recap: let's go with your ROWNUM > 10: once a record is otherwise qualified, Oracle will assign it ROWNUM of 1 for the first record, then it will have to "re-qualify" the record base on "ROWNUM > 10"; since the assigned ROWNUM is 1, it fails the "ROWNUM > 10" so this record is discarded. On the second record, Oracle will re-assign the ROWNUM as 1 because ROWNUMs are contiguous; it does the same evaluation of whether the assigned ROWNUM is greater than 10, and failing which, the record is again discarded. In fact, no records will pass the "ROWNUM > 10" because every previous record was discarded, so the assigned ROWNUM value is always 1 when Oracle tries to evaluate whether the record's "ROWNUM > 10") As a result, your reference to "ROWNUM > 10" will always produce zero records, irrespective of what the subquery may or may not return.
The solution you probably want is to alias the ROWNUM column in the subquery, and then reference the aliased column name rather than the ROWNUM from the enclosing query:
select r,
GENDER
from (select ROWNUM r,
GENDER
from EMP2
where ROWNUM <= 20)
where r > 10;
You can see the differences in the ROWNUM with this:
select ROWNUM, -- this is from the enclosing query
R, -- this is from the subquery
GENDER
from (select ROWNUM R,
GENDER
from EMP2
where ROWNUM <= 20)
where r > 10;
..Rudy
-----Original Message-----
Sent: Wednesday, July 09, 2003 12:19 PM
To: Multiple recipients of list ORACLE-L
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)
SQL> SELECT r, GENDER
2 FROM (SELECT ROWNUM r, GENDER
3 FROM EMP2 4 WHERE ROWNUM <= 20)
SQL> SELECT r "ROWNUM", GENDER
2 FROM (SELECT ROWNUM r, GENDER
3 FROM EMP2 4 WHERE ROWNUM <= 20)
SQL>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rudy Zung
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). Received on Wed Jul 09 2003 - 10:55:26 CDT
 |
 |