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);
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
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 )