Rownum is pseudocolumn of the placement of the Row in the result set,
and cannot be used in a equality in the where clause except for use in <
or <=

Use of Rownum = 3 implies that the number is assigned before the final
result set is established.

The only way around this is nested view projection using the rownum as a
Return Column

Example


select object_name, rownum from sys.dba_objects where objecT_name in
('DBA_TABLES','DBA_INDEXES','DBA_SEGMENTS') and owner = 'SYS'
order by 1;

OBJECT_NAME
------------------------------------------------------------------------
--------
ROWNUM
----------
DBA_INDEXES
2

DBA_SEGMENTS
1

DBA_TABLES
3


select object_name
from (select object_name, rownum from sys.dba_objects where objecT_name
in ('DBA_TABLES','DBA_INDEXES','DBA_SEGMENTS') and owner = 'SYS'
order by 1) where rownum = 3;

no rows selected

select object_name
from (select object_name, rownum row_num
from sys.dba_objects where objecT_name in
('DBA_TABLES','DBA_INDEXES','DBA_SEGMENTS')
and owner = 'SYS'
order by 1)
where row_num = 3;

OBJECT_NAME
------------------------------------------------------------------------
--------
DBA_TABLES



Tony V

-----Original Message-----
From: Holmes LW (Lance) at Aera
[mailto:oracledba-ezmlmshield-x10409444.[Email address protected]
Sent: Tuesday, March 06, 2007 4:05 PM
To: LazyDBA Discussion
Subject: RE: problem with rownum pseudocolumn

Vikas,

I think rownum is assigned to the returning rows, so =1 will work, or <=
3 will work, but if the query only returns 1 row (=3) there is no rownum
3. We played around with this here also. I was hoping someone else would
give you a better answer.


Thanks,
Lance


-----Original Message-----
From: Vikas
[mailto:oracledba-ezmlmshield-x29709559.[Email address protected]
Sent: Sunday, March 04, 2007 8:57 PM
To: LazyDBA Discussion
Subject: problem with rownum pseudocolumn


Hi, I am facing a problem in a SQL query. When I give the query

SELECT name, salary
FROM (SELECT * FROM sal ORDER BY salary DESC) WHERE ROWNUM < 6;

This displays me the first 5 highest salaries and names. But when I
modify this query as:-

SELECT name, salary
FROM (SELECT * FROM sal ORDER BY salary DESC) WHERE ROWNUM =3;

To find out the 3rd highest salary then it says zero rows selected. Why
this is happening? Please help me.



DISCLAIMER:
This message contains privileged and confidential information and is
intended only for an individual named. If you are not the intended
recipient, you should not disseminate, distribute, store, print, copy or
deliver this message. Please notify the sender immediately by e-mail if
you have received this e-mail by mistake and delete this e-mail from
your system. E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete or contain viruses. The sender,
therefore, does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required, please request a hard-copy version.


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




Oracle LazyDBA home page