mediumSubqueries and CTEsNULL handlingSQL
Second Highest Salary
Return the second highest distinct salary, or NULL when there isn't one. Tests DISTINCT, LIMIT and OFFSET, subqueries and NULL handling in SQL.
SQL
The table employees(id, name, salary, manager_id, dept_id) holds every employee's salary.
Write a query that returns the second highest distinct salary as a single row with one column, second_highest. If there is no second highest salary (no employees, or everyone earns the same), return a single row containing NULL.
Examples
Example 1
Input: salaries: 3000, 5000, 4000 Output: second_highest 4000 Why: The distinct salaries in order are 5000, 4000, 3000.
Example 2
Input: salaries: 5000, 5000 Output: second_highest NULL Why: There is only one distinct salary, so there is no second highest.
Constraints
- Always return exactly one row.
- Ties count once: 5000, 5000, 4000 has second highest 4000.
- The table can be empty.
Hints
Hint 1
DISTINCT plus ORDER BY salary DESC puts the distinct salaries in order.
Hint 2
LIMIT 1 OFFSET 1 picks the second one, but returns zero rows when there isn't one.
Hint 3
Wrapping a query that returns no rows in a scalar subquery turns it into NULL.
Solution
Show the solution (try it yourself first)
Approach: scalar subquery with OFFSET
Sort the distinct salaries from highest to lowest and skip one. On its own, LIMIT 1 OFFSET 1 returns no row when there is no second salary, but the problem asks for a row with NULL. Wrapping the query in a scalar subquery solves that: a scalar subquery that finds nothing evaluates to NULL, and the outer SELECT without a FROM always returns one row.
SELECT (
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1
) AS second_highest;An alternative without LIMIT: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). MAX over no rows is NULL, so it also meets the rule. For the general Nth highest, use DENSE_RANK().
Complexity
O(n log n) to sort, or one index scan if salary is indexed (MySQL can read the index backwards).
Found a mistake in this problem, its tests or the solution?