The second highest, with ties
Find the Nth highest value, counting distinct values, and return NULL when there isn't one. About 10 minutes.
A value, not a row
"The second highest salary" sounds like "sort and take the second row". With ties, that gets it wrong: if two people earn 90, the second row is also 90, and 90 is the highest, not the second highest.
The question is about values. Take the distinct values first, sort them from the top, and skip one. Then the second row of that list is the second highest value.
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
| name | salary |
|---|---|
| Asha | 90 |
| Ravi | 90 |
| Meera | 70 |
| Kabir | 50 |
Asha and Ravi both earn 90. As distinct values they are one: 90, the highest.
When there isn't one
If every salary is the same, or the table is empty, there is no second highest value. That query returns no row at all, and an interviewer will ask for NULL.
Put the query in brackets as a subquery in SELECT. A subquery that finds no row becomes NULL, so the answer is always exactly one row. DENSE_RANK works too, but is heavier than you need here.
SELECT ( SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1 ) AS second_highest;
The salaries are 90, 90 and 70. What is the second highest salary?
- A70
- B90
- CNULL
Show the answer
70. The distinct values are 90 and 70. The second one is 70.
Second highest salary
The employees table has one row per employee. Show the second highest distinct salary as second_highest. If there is no second highest salary, show NULL. The answer is always exactly one row.
employees(id, name, salary)
Up to 2,000 employees