Return the second-highest salary in each department
Table employees(department, salary). Return the second-highest salary in each department. Treat equal salaries as one rank, so you want the second-highest distinct value. A department with only one salary level returns nothing.
-- second-highest distinct salary per departmentWrite the query.
Rank salaries per department, then filter to rank 2 outside — a window result can't go in WHERE. DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) ties equal salaries, so rank 2 is the 2nd distinct value.
- ✗Using
ROW_NUMBER, which splits tied top salaries and shifts rank 2 - ✗Applying
LIMIT/OFFSETglobally instead of per department - ✗Forgetting the window result must be filtered in an outer query
- →How would the answer change if you wanted the second-highest employee, ties included?
- →Why does
DENSE_RANKbeatROW_NUMBERwhen salaries can tie?
Rank salaries within each department, then keep rank 2 in an outer query — a window function cannot be filtered directly in WHERE:
SELECT department, salary
FROM (
SELECT department, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk = 2;DENSE_RANK collapses equal salaries, so rank 2 is the second-highest distinct value (not the second employee). ROW_NUMBER would give tied top salaries different numbers, and rn = 2 could point at a second employee sharing the same maximum. LIMIT 1 OFFSET 1 works globally and does not partition by department.
