nth Highest Salary from EMP table , need explaination
739995Dec 9 2009 — edited Dec 10 2009I 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.