learn deep. ship safe.Practice testsFollow ByteMunSubscribe

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?

Written by Published Tests checked by Deepak
SQL · standard syntax, runs on SQLite
Case 1
Table employees
idnamesalarymanager_iddept_id
1A3000NULL1
2B5000NULL1
3C4000NULL1
Expected (1 row)
4000
Case 2
Table employees
idnamesalarymanager_iddept_id
1A5000NULL1
2B5000NULL1
Expected (1 row)
NULL

Run checks these 2 samples. Submit checks all 6 tests, including hidden ones.

  1. Daily Sales Running TotalWindow functions · GROUP BY and aggregates · SQL
    medium
  2. Highest Paid Employee in Each DepartmentJoins · GROUP BY and aggregates · Window functions · SQL
    medium
  3. Find Duplicate EmailsGROUP BY and aggregates · SQL
    easy
  4. easy