You have to think of ROWNUM only being updated when the row is displayed
(although you're not always displaying it). So when you choose ROWNUM<6
then the first row is displayed and gets a rownum of 1, then the second
one gets a rownum of 2 etc. up to 5.

When you ask for Rownum=3 then it never happens because 1 and 2 are
never displayed. It's one of those things you need to get your mind
around.

You can use Ed's method or using simple, old school, logic you could add
another level...

select name, salary from (
SELECT name,sal, rownum AS rnum
FROM ( SELECT * FROM sal ORDER BY sal DESC ) )
Where rnum = 3;


Stephen Weston



-----Original Message-----
From: Vikas
[mailto:oracledba-ezmlmshield-x29709559.[Email address protected]
Sent: 05 March 2007 04:57
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



Oracle LazyDBA home page