Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

nth Highest Salary from EMP table , need explaination

739995Dec 9 2009 — edited Dec 10 2009
I am new to the database world and hence learning queries at the initial stages. I want to retrieve the nth highest salary from the EMP table. I was not able to find out the answer by myself , so I searched on google. The following was the answer i got :

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

Enter value for n: 2
SAL
---------
3700

Suppose the table contains salary in the following order :

SAL
--------
2000
3000
3700
4000
3700
2000

So how come I will get the correct answer with the abov query ? I am not able to understand the part --- WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

Specially the condition WHERE a.sal<=b.sal is very confusing to me. Can anyone help me please to understand the same.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 7 2010
Added on Dec 9 2009
13 comments
2,968 views