SQL query to find Nth highest salary

database, sql

Solution

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
            )

Problem

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.

Original source

Related problems