Vishal - 1 year ago 66
SQL Question

# SQL query to find Nth highest salary

I am referring to following query to find Nth highest salary of a employee.

``````select sal from emp t where &n = (select count(sal) from (select distinct sal
from emp) where t.sal<=sal);
``````

One gentleman said this query works. Could someone please explain how equating a COUNT ( which really will be value between 1 to X where X is total distinct salaries) to &n produce this result ?

I am trying to understand how database handles this query internally and produces result ?

Thank you.

First, the query will return the `nth` lowest salary value. To return the `nth` highest salary value you must change `t.sal <= sal` to `t.sal >= sal`.

Next, this query works by first finding the distinct list of salary values as one derived table and then determines the number of employees that have a salary less than each one in this list. `t.sal <= sal` is taking the derived table (which most databases would require have an alias) and comparing each value against the outer `emp` table. It should be noted that this will return multiple rows in the case of a tie.

To manually trace the output, we need some inputs:

``````Alice       | 200
Bob         | 100
Charlie     | 200
Danielle    | 150

Select Distinct sal
From emp
``````

Gives us

``````200
100
150
``````

Now we analyze each row in the outer table

``````Alice - There are 3 distinct salary values less than or equal to 200
Bob - 1 rows <= 100
Charlie - 3 rows <= 200
Danielle - 2 row <= 150
``````

Thus, for each salary value we get the following counts (and reordered by count):

``````Bob 1
Danielle 2
Charlie 3
Alice 3
``````

The most important aspect that I think you are overlooking is that the outer `emp` table is correlated to the inner count calculation (which is why it is called a correlated subquery). I.e., for each row in the outer `emp` table, a new count is calculated for that row's salary via `t.sal <= sal`. Again, most database systems would require the inner most query to have an alias like so (note the `As Z` alias):

``````Select sal
From emp As t
Where &n =  (
Select Count(Z.sal)
From    (
Select Distinct sal
From emp
) As Z
Where t.sal <= Z.sal
)
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download