Second Highest Salary

Question

Write a SQL query to get the second highest salary from the Employee table.

    | Id | Salary |
    | 1  | 100    |
    | 2  | 200    |
    | 3  | 300    |

For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

    | SecondHighestSalary |
    | 200                 |

This is a question from Leetcode, for which I entered the following code:

    SELECT CASE WHEN Salary = '' 
                THEN NULL
    ELSE Salary 
    END AS SecondHighestSalary 
    FROM (SELECT TOP 2 Salary
                ,ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Num
          FROM Employee
          ORDER BY Salary DESC) AS T
    WHERE T.Num = 2

It says that the query does not return NULL if there's no value for second highest salary. For eg. if the table is

   | Id | Salary| 
   | 1  |  100  |

The query should return

   |SecondHighestSalary|
   |       null        |

and not

   |SecondHighestSalary|
   |                   |

Answer 1

In case of ties you want the second highest distinct value. E.g. for values 100, 200, 300, 300, you want 200.

So get the highest value (MAX(salary) => 300) and then get the highest value less than that:

select max(salary) from mytable where salary < (select max(salary) from mytable);