Hi Vikas,

The problem is that rownum is applied to the rows selected. In your query it finds the first row, rejects it because it's not row 3, finds the second row, rejects it because it's not row 3, finds the third row and ALSO rejects it because it's still not the third row - it hasn't even got as far as the first row, certainly not to the third.

What you need to do is put the rownum in and alias it, and then select further out on this rownum. In theory, you should be able to do:

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

However, I have problems doing this because when I sort DESC, my rownums end up counting down i/o up, and I get the third lowest salary. It must be applying the rownum before applying the DESC.

The solution is, unfortunately, to run 3 nested queries:

SELECT name, salary
FROM
(SELECT name, salary, rownum x FROM
(SELECT name, salary FROM sal ORDER BY salary DESC)
) WHERE x = 3;

Proper DBAs will show you other strange functions, and they probably run quicker. I have no idea about them - I'm a developer, not a DBA at all!! But that's why you're getting the problem you're getting.

-----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