Second Highest Salary
The finance team wants the second highest distinct monthly salary paid to anyone in the staff table, for a pay-band review.
Return one row with one column named second_highest. If there is no second distinct salary (for example, everyone earns the same), return NULL in that row.
Tables
staff — One row per employee.
| Column | Type | Description |
|---|---|---|
staff_id (key) | INTEGER | Unique id |
full_name | TEXT | Employee name |
monthly_pay | INTEGER | Monthly salary in dollars |
Example
staff
| staff_id | full_name | monthly_pay |
|---|---|---|
| 1 | Asha | 4200 |
| 2 | Ben | 6100 |
| 3 | Chen | 5300 |
| 4 | Dana | 6100 |
Expected output
| second_highest |
|---|
| 5300 |
Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.
SQL Path · Subqueries & CTEs · problem 31 of 70 · SQLite
The idea
Sort the distinct salaries from high to low, skip the first and take the next: LIMIT 1 OFFSET 1. On its own that query returns no row when there is no second salary, but the task wants one row containing NULL.
Wrapping it as a scalar subquery, SELECT (…) AS second_highest, fixes that: a scalar subquery that finds nothing evaluates to NULL, and the outer SELECT always produces exactly one row.
The alternative takes the largest salary that is strictly below the maximum. MAX over zero rows is also NULL, so it handles the edge cases for free.
Solution
SELECT (
SELECT DISTINCT monthly_pay
FROM staff
ORDER BY monthly_pay DESC
LIMIT 1 OFFSET 1
) AS second_highest;Another way
SELECT MAX(monthly_pay) AS second_highest
FROM staff
WHERE monthly_pay < (SELECT MAX(monthly_pay) FROM staff);Why common answers fail
Skips DISTINCT, so a tie at the top returns the top salary again, and an empty result instead of NULL when there is no second salary.
SELECT monthly_pay AS second_highest FROM staff ORDER BY monthly_pay DESC LIMIT 1 OFFSET 1;Handles ties but returns no row at all when there is no second salary, instead of one row with NULL.
SELECT DISTINCT monthly_pay AS second_highest FROM staff ORDER BY monthly_pay DESC LIMIT 1 OFFSET 1;What the interviewer is checking
Interviewers use this to see whether you think about duplicates and empty results before you are told about them. Both are everyday sources of reporting bugs.
Mistakes to avoid
- Forgetting DISTINCT, so a tie at the top is reported as the second highest.
- Returning zero rows instead of a single NULL row.
- Using OFFSET without ORDER BY, which gives an arbitrary row.
Follow-ups to expect
- Return the N-th highest salary for any N (window function DENSE_RANK).
- Second highest per department (PARTITION BY department).
- What if salaries can be NULL? (Filter them out first.)
In real work
Pay-band reviews, runner-up leaderboards and "next best price" lookups all ask for the next distinct value with a sensible empty answer.