select dr rank, sal, ename
from (
select sal,ename,
row_number()over(partition by sal order by sal desc) rn,
dense_rank()over(order by sal desc) dr
from emp
)
where rn = 1 and dr <= 5
/





Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Jeanine Blomberg is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below. Thank you in advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




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

Oracle LazyDBA home page