# Find the second highest salary of employees

Imagine there is a table with these fields:

``````Employee: E-Number
E-Name
Department
Salary
``````

We need to write a query in order to find the second highest salary in this table.

I found this query :

``````select distinct salary
from Employee e1
where 2 = ( select count(distinct salary)
from Employee e2
where e1.salary < e2.salary)
``````

I'm a bit confused by this part `where 2 =` because I have not seen something like that before.

Can anyone here explain more about the query and the part `where 2 =`? What is the result of the subquery and in what way are table rows (e1, e2) being compared with each other?

Actually this is the execution plan can anyone explain the steps more? I'm going to address this part of the question:

...this is the execution plan can anyone explain the steps more?

Assuming the table definition looks something like this:

``````CREATE TABLE dbo.Employee
(
[E-Number]  integer NOT NULL,
[E-Name]    nvarchar(50) NOT NULL,
Department  nvarchar(30) NOT NULL,
Salary      smallmoney NOT NULL
);
``````

### Empty table

The following query (not my recommendation, just an example):

``````SELECT DISTINCT
E1.Salary
FROM dbo.Employee AS E1
WHERE
1 =
(
SELECT
COUNT(DISTINCT E2.Salary)
FROM dbo.Employee AS E2
WHERE
E2.Salary > E1.Salary
);
``````

Gives this execution plan (with no data in the table): The numbers correspond to the Node ID property of each operator.

• The `E1` Table Scan (4) returns all rows (in no particular order). For each row:
• The Nested Loops join (3) executes the inner side, passing the current `E1.Salary` value as a parameter.
• The `E2` Table Scan (8) finds rows matching `E2.Salary > E1.Salary`
• The Distinct Sort (7) groups rows by `E2.Salary`.
• The Stream Aggregate (6) counts the number of rows (1 per distinct `E2.Salary`). The new count column is given the label `Expr1008`.
• The Compute Scalar (5) converts the native count data type (`bigint`) to `integer` because the query specifies `COUNT` rather than `COUNT_BIG`. The new column is given the label `Expr1004`.
• As rows emerge from the Nested Loops join, the Filter (2) applies the predicate `Expr1004 = 1`
• The final Distinct Sort returns the distinct `E1.Salary` values.

### With duplicate Salary values

If we add a few rows to the table, with some duplicate `Salary` values:

``````INSERT dbo.Employee
([E-Number], [E-Name], Department, Salary)
VALUES
(1, 'A', 'D1', \$1),
(2, 'B', 'D1', \$1),
(3, 'C', 'D1', \$2),
(4, 'D', 'D1', \$2),
(5, 'E', 'D1', \$3),
(6, 'F', 'D1', \$3);
``````

The query above produces a plan with a couple of new operators (highlighted): The new Sort orders rows by `E1.Salary`. The ensures that any duplicate `Salary` values are presented to the join sequentially.

The Lazy Index Spool saves the output from the operators below it as they arrive. These are the Stream Aggregate, Distinct Sort, and `E2` Table Scan (with predicate), which as described before filter `E2` (using the current value of `E1.Salary`), remove duplicate `E2.Salary` values, and count the resulting rows. The Lazy Index Spool saves recomputing the result in case the same `Salary` value from table `E1` is presented on the next iteration of the Nested Loops join.

Because the Sort ordered rows from `E1` by `E1.Salary`, any duplicates are presented to the join one after the other, so the saved result in the spool can be reused instead of re-running the Stream Aggregate, Distinct Sort, and `E2` Table Scan each time.

The index on the spool is keyed by `Salary`, making it quick to find the saved `COUNT(DISTINCT E2.Salary)` result for the current `E1.Salary` value.

If you look at the Actual Executions value for each operator in the plan, you will see that the Lazy Index Spool executes 6 times (once per outer row), whereas the Stream Aggregate, Distinct Sort, and `E2` Table Scan execute only 3 times (once per distinct salary value).

The optimizer introduces the extra Sort and Spool as a performance optimization, based on automatically collected statistical information about the data in the table.

Well, to begin with, this code snippet is not entirely correct. For the second highest salary, it should be

``````where 1 = ( select count(distinct salary)
from Employee e2
where e1.salary < e2.salary)
``````

because this sub query is basically counting salaries which are higher than a current value, and if you want second highest salary, there must only be one value which is higher.

Maybe this will also help you to understand. Try running the query for `where 0 =`, it will return the highest salary because none of the employees have a higher salary than the highest one.

Alternatively you can keep the `2 =` but change the `e1.salary < e2.salary` condition to `e1.salary <= e2.salary` to produce the correct result, i.e. like this:

``````where 2 = ( select count(distinct salary)
from Employee e2
where e1.salary <= e2.salary)
``````