database - SQL query to find Nth highest salary

Question

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.

Answer 1

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
            )