MiddleCodeCommonNot answered yet
Write a query for the second-highest salary
Return the second-highest distinct salary, or NULL if there is no second one.
-- Employee(id, name, salary, dept_id, manager_id)
-- write your query hereWrite the query.
Take the max salary strictly below the overall max: SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee). This returns NULL cleanly when there is no second salary. Alternative: ORDER BY salary DESC LIMIT 1 OFFSET 1 over DISTINCT salaries.
- ✗Forgetting
DISTINCT, so duplicated top salaries break the offset approach - ✗Putting an aggregate like
MAX()directly in aWHEREclause - ✗Confusing a salary value with its rank/ordinal position
- →How would you generalize this to the N-th highest salary?
- →Why does the subquery version return
NULLrather than an empty result for a single salary?
Contents
Task
Return the second-highest distinct salary from Employee, or NULL if there is none.
Solution
SELECT MAX(salary) AS second_highest
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);Key points
- The subquery finds the overall max; the outer
MAXtakes the largest remaining salary. - With no second salary,
MAXover an empty set yieldsNULL— no error. - Window alternative:
DENSE_RANK() OVER (ORDER BY salary DESC)filtered to= 2.
Contents
