The second highest salary
HR is putting together a compensation benchmarking report: “What’s the highest salary at NOVA?” Harry answers that one without thinking — MAX(salary). Then HR follows up: “Okay. What if I want the second highest, not the highest?”
Second highest... isn't that just MAX again, minus one row?
There's no 'minus one row' in SQL. MAX gives you one number: the biggest. You need a different question entirely.
Reword it: the second-highest salary is the highest salary among everyone who isn’t already at the very top. That’s a question inside a question — first find the highest, then find the highest of what remains once that’s excluded. Exactly the shape a subquery is for.
The best of what’s left after removing the best.
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);| second_highest |
|---|
| 150000 |
That works, and it generalizes cleanly — but it only gives you the number, not who earns it. There’s a more direct route if you want the row too: sort everyone by salary, then skip past the top one instead of comparing against it. OFFSET tells SQL how many rows to skip before it starts counting LIMIT.
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;| name | salary |
|---|---|
| Lena Fischer | 150000 |
Same answer, this time with a name attached: Lena Fischer, one row below Meera Rao’s $165,000.
Harry: “So for the fifth highest, I just change it to OFFSET 4?”
Let’s check, rather than assume.
SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 4;| salary |
|---|
| 128000 |
$128,000. But look at the actual distinct salaries at NOVA, in order: $165k, $150k, $140k, $128k, $121k… the fifth distinct value is $121,000. Two people happen to earn $128,000, so it occupies two rows — and OFFSET counts rows, not distinct values. It landed on the second $128k row and called it the “fifth highest,” which it isn’t.
Adding DISTINCT before ranking fixes it:
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 4;| salary |
|---|
| 121000 |
That’s correct now — but it also happens to erase who earns it, since DISTINCT only keeps the salary column. For “second highest” specifically this trap never bit us, because the single highest salary at NOVA isn’t tied with anyone. It would have, the moment two people shared the top spot.
What you actually want is a rank that treats ties as one position — two people at $128,000 both get rank 4, and whoever’s next still gets rank 5, no gap, no row-counting trap. That’s exactly DENSE_RANK.
SELECT name, salary
FROM (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 5;| name | salary |
|---|---|
| Tom Becker | 121000 |
| Ken Watanabe | 121000 |
$121,000, correctly — and it returns both Tom and Ken, because they genuinely tie for that rank. A single OFFSET row can never tell you that two people share a position; a rank can. Swap rnk = 5 for rnk = 2 and you’re back to the original question — same $150,000, same Lena Fischer, now derived from a pattern that scales to any N without a silent bug hiding in it.
Running…
Try it: What if you use RANK instead of DENSE_RANK for rnk = 2?
Run your query to see results here.
Same result here, because nobody ties for first place at NOVA. But RANK and DENSE_RANK aren’t interchangeable in general: RANK leaves a gap after a tie (two people tied for 1st means the next rank is 3, not 2), while DENSE_RANK never skips. If NOVA hired someone tomorrow at $165,000, tied with Meera for the top salary, RANK would say there’s no rank 2 at all — jumping straight to 3 — while DENSE_RANK would still correctly call $150,000 rank 2. For “the Nth highest,” DENSE_RANK is almost always the one you want.
Engineering’s manager wants to know the third-highest salary within the Engineering department — and who earns it. Return name and salary for everyone at that rank.
Hint 1
This is the second-highest pattern, generalized to N=3 and scoped to one department.
Hint 2
DENSE_RANK() OVER (ORDER BY salary DESC) inside Engineering handles ties correctly — MAX-based subqueries and OFFSET both get harder to trust once you go past N=2.
Hint 3
Filter to department = 'Engineering' before ranking, wrap the ranked rows in a subquery or CTE, then keep WHERE rnk = 3.
Solution
Running…
Engineering’s salaries, top to bottom: $165k (Meera, alone), $128k (Sam and Priya, tied for 2nd), $121k (Tom and Ken, tied for 3rd). The third-highest salary really is shared by two people — and a query built on DENSE_RANK reports that honestly instead of arbitrarily picking one of them.
“Nth highest” isn’t MAX with an adjustment — it’s a comparison (WHERE value < the max) for the simplest case, or a rank for the general case. DENSE_RANK is the safe default once ties are possible, because OFFSET counts rows and ties make row count and rank diverge.