Vishal - 1 year ago 39

SQL 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 Source

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