Find the second-highest salary, handling the no-second case
Table Employee(id, name, salary). Return the second-highest distinct salary.
Requirements:
result should be well-defined; a NULL-returning form is preferred over an empty set
- duplicate top salaries must not shift the answer — rank by distinct value
- when there is no second salary (all rows share one value, or only one row), the
SELECT MAX(salary) AS second_highest
FROM Employee
-- your query hereWrite the query.
Take MAX(salary) where salary < (SELECT MAX(salary) FROM Employee): the inner max is the top salary, so the outer max is the runner-up. With no second salary this returns a single NULL row cleanly. The ORDER BY salary DESC LIMIT 1 OFFSET 1 variant instead returns no rows.
- ✗Assuming
LIMIT 1 OFFSET 1matches theMAX < MAXsubquery on tied top salaries - ✗Forgetting the
LIMIT/OFFSETform returns no rows whileMAX(... < MAX)returnsNULL - ✗Not deduplicating salaries, so duplicate top values shift the result
- →How do duplicate top salaries change the
LIMIT/OFFSETresult versus the subquery? - →Why does the
MAX(... < MAX)form returnNULLinstead of an empty set?
Task
Find the second-highest salary in Employee(id, name, salary, dept_id, manager_id).
-- Subquery: the max among salaries below the largest one
SELECT MAX(salary) AS second_highest
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);
-- LIMIT/OFFSET variant (behaves differently when there is no second)
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;How it works
The inner SELECT MAX(salary) finds the largest salary. The outer MAX(salary) with salary < ... takes the maximum of what remains — that is the second-highest.
The difference shows up in the edge case (a single distinct salary):
- the
MAX(... < MAX)form returns one row holdingNULL; - the
LIMIT 1 OFFSET 1form returns zero rows.
⚠️ DISTINCT matters: without it, duplicate top salaries shift the OFFSET and return the wrong row.
