How Do You Find the Second Highest Salary in SQL?
Learn how to find the second highest salary in SQL using subqueries and DENSE_RANK(), and why LIMIT/OFFSET can fail with tied top salaries here.
Expected Interview Answer
The most robust way is to use a window function like DENSE_RANK() ordered by salary descending and select the row where the rank equals 2, which correctly handles ties, though a simpler subquery approach using MAX(salary) WHERE salary < (SELECT MAX(salary) FROM employees) also works for the common case.
The subquery pattern `SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)` finds the highest salary strictly below the overall maximum, which correctly returns a single second-highest value even if multiple employees tie for first place. An alternative uses `LIMIT`/`OFFSET` with `ORDER BY salary DESC LIMIT 1 OFFSET 1`, but this can give a wrong answer if there are duplicate top salaries, since offsetting by row count treats ties as separate rows rather than separate ranks. The most general and interview-favored solution uses DENSE_RANK() in a window function: rank all salaries in descending order allowing ties to share a rank, then filter for rank = 2, which correctly generalizes to finding the Nth highest salary by changing the rank filter.
- DENSE_RANK() correctly handles duplicate top salaries
- The subquery MAX-less-than pattern works without window function support
- Generalizes cleanly to the Nth highest salary by changing the filter
- Avoids the OFFSET pitfall of miscounting ties as separate ranks
- A common, high-signal interview question testing ranking logic
AI Mentor Explanation
Finding the second highest salary is like finding the second-highest individual score in an innings when two batters might have tied for the top score. Simply taking the score at 'position 2 down the batting card' fails if two players tied at the top, because that pushes the real second-best score down to position 3 on the card.
Step-by-Step Explanation
Step 1
Recognize the tie pitfall
A naive LIMIT/OFFSET or row_number approach can miscount when multiple employees share the highest salary.
Step 2
Try the subquery approach
SELECT MAX(salary) WHERE salary < (SELECT MAX(salary) FROM employees) finds the next distinct value below the max.
Step 3
Prefer DENSE_RANK() for generality
Rank all salaries descending with DENSE_RANK(), letting tied values share a rank, then filter WHERE rnk = 2.
Step 4
Wrap the window function in a subquery/CTE
Window functions can't be filtered directly in the same SELECT's WHERE, so wrap it in a CTE or subquery first.
Step 5
Generalize to Nth highest
Changing the filter to rnk = N (or the MAX-less-than pattern chained N-1 times) finds any Nth highest value.
What Interviewer Expects
- Identifies that ties in the top salary are the key edge case to handle
- Can write both the subquery and window-function solutions
- Explains why LIMIT/OFFSET can be wrong with duplicate top values
- Knows DENSE_RANK() vs RANK() vs ROW_NUMBER() differences
- Can generalize the solution to find the Nth highest value
Common Mistakes
- Using ROW_NUMBER() instead of DENSE_RANK(), breaking ties incorrectly
- Assuming LIMIT 1 OFFSET 1 always works regardless of duplicate salaries
- Filtering a window function directly in the same query's WHERE clause (not allowed)
- Forgetting to specify ORDER BY DESC inside the window function
Best Answer (HR Friendly)
“You rank all salaries from highest to lowest using a window function that lets tied values share a rank, then pick out the row at rank 2 — this correctly handles cases where multiple people share the top salary, unlike simpler approaches that assume no ties exist.”
Code Example
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);WITH ranked AS (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT DISTINCT salary FROM ranked WHERE rnk = 2;Follow-up Questions
- How would you modify the query to find the Nth highest salary?
- What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
- Why might ORDER BY salary DESC LIMIT 1 OFFSET 1 give a wrong answer?
- How would you find the second highest salary per department?
- How would you handle NULL salary values in this query?
MCQ Practice
1. Why can LIMIT 1 OFFSET 1 give a wrong second-highest salary?
OFFSET counts rows, not distinct ranks, so ties at the top push the real second value further down.
2. Which window function correctly lets tied salaries share the same rank?
DENSE_RANK() assigns the same rank to tied values and continues ranks without gaps, unlike ROW_NUMBER().
3. What does this subquery return: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)?
It finds the highest salary strictly below the overall maximum, i.e., the second highest distinct value.
Flash Cards
Subquery for 2nd highest salary — MAX(salary) WHERE salary < (SELECT MAX(salary) FROM employees)
Best window function for ties — DENSE_RANK() — shares rank across tied values without gaps.
Pitfall of LIMIT/OFFSET — Miscounts when duplicate top salaries exist.
Generalizing to Nth highest — Filter DENSE_RANK() output WHERE rnk = N.