Recap — Why People Get Nth Highest Wrong

# Why People Get Nth Highest Wrong The classic prompt: *"From the employees table, find the second highest salary."* Sounds trivial. It's interviewer-favourite because three different interpretations all sound right, and the wrong one fails on edge cases. ## The naive answer (don't write this) ```sql SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); ``` Returns the second-highest *distinct* salary. Works if there are no ties. Fails when: - Two employees tie for the highest — this returns the *third* highest from the rest, not the second highest. - No second salary exists — returns NULL, which the interviewer may or may not want. ## The DENSE_RANK answer (the safe default) ```sql WITH ranked AS ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) SELECT MAX(salary) FROM ranked WHERE rnk = 2; ``` DENSE_RANK gives ties the same rank with no gaps. `rnk = 2` is the second-highest distinct salary. Handles ties cleanly. ## The LIMIT/OFFSET answer (cheaper but distinct-aware) ```sql SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; ``` Same intent: second-highest distinct salary. The DISTINCT is the key — without it, two tied first-place salaries make this return the first, not the second. ## "What if the second highest doesn't exist?" The interviewer might want NULL, or might want you to handle it. Wrap with a subquery: ```sql SELECT ( SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1 ) AS second_highest; ``` The outer SELECT returns NULL when the inner query has no rows. Most interviewers want exactly this. ## Parameterising N ```sql WITH ranked AS ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) SELECT MAX(salary) FROM ranked WHERE rnk = :n; ``` DENSE_RANK works for any N. Note: changing to RANK gives different semantics — if the top 3 tie, RANK = 2 won't exist, but DENSE_RANK = 2 returns the next distinct value. ## The "per department" variant The same problem with a twist: *"Find the second-highest salary per department."* ```sql WITH ranked AS ( SELECT department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk FROM employees ) SELECT department_id, salary FROM ranked WHERE rnk = 2; ``` `PARTITION BY department_id` resets ranking per department. Almost always the right SQL when the question has "per X" in it. ## Three "gotcha" follow-ups interviewers love 1. *"What if salaries can be NULL?"* — NULLs sort last with DESC by default in most databases. If you need them excluded, add `WHERE salary IS NOT NULL` in the CTE. 2. *"What if two employees have identical salaries — return both?"* — Don't use MAX in the outer SELECT. Return all rows where `rnk = 2`. 3. *"What's the difference between RANK and DENSE_RANK for this question?"* — RANK leaves gaps after ties (1, 1, 1, 4). DENSE_RANK doesn't (1, 1, 1, 2). For "Nth highest distinct", DENSE_RANK is right. ## Takeaway Default to DENSE_RANK. It's the answer for ~80% of "Nth highest" variants. The naive `MAX < MAX` approach silently breaks on ties — never write it in real interviews.