How Do You Find the Second Highest Salary in SQL?
Learn two ways to find the second highest salary in SQL using a MAX subquery and DENSE_RANK window function, with examples, edge cases and interview tips.
Expected Interview Answer
You find the second highest salary by excluding the maximum salary and taking the max of what remains, or by ranking salaries with DENSE_RANK() and selecting rank 2. Both approaches correctly return the distinct second-largest value.
A classic portable solution is SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees), which finds the largest salary below the top one. A modern, more flexible approach uses window functions: DENSE_RANK() OVER (ORDER BY salary DESC) assigns 1 to the top distinct salary, 2 to the next, and you filter for rank 2. DENSE_RANK is preferred over ROW_NUMBER or RANK because it handles ties correctly and does not skip the second position. Always decide whether you want the second highest distinct value or the second row, and handle the case where no such value exists (return NULL).
- DENSE_RANK generalizes to the Nth highest by changing one number
- The subquery form is portable across almost all SQL databases
- Correctly returns NULL when a second salary does not exist
- Handles duplicate salaries when you want distinct values
- Avoids fragile approaches like LIMIT with OFFSET on ties
AI Mentor Explanation
To name the second-highest run scorer in an innings, you first find the top scorer, mentally set them aside, then look for the biggest total among everyone left. If two players tie at the top you still want the next distinct score below them. That two-step of removing the maximum and taking the max of the rest is exactly how the classic second-highest-salary query works.
Step-by-Step Explanation
Step 1
Find the maximum
Compute the highest salary with SELECT MAX(salary) FROM employees as the value to exclude.
Step 2
Exclude the top value
Filter rows where salary is strictly less than that maximum so all top-tier and tied rows are removed.
Step 3
Take the max of the rest
Apply MAX() again over the remaining rows to get the second highest distinct salary.
Step 4
Or rank with a window function
Compute DENSE_RANK() OVER (ORDER BY salary DESC) so each distinct salary gets a rank.
Step 5
Select rank 2
Wrap the ranked result and filter WHERE rnk = 2, changing the number to get any Nth highest salary.
Step 6
Handle the empty case
Both approaches return NULL when there is no second distinct salary; confirm that behavior meets requirements.
What Interviewer Expects
- At least one correct, runnable solution
- Knowledge of both the subquery and window-function approaches
- Why DENSE_RANK is preferred over ROW_NUMBER or RANK for ties
- Handling of duplicate salaries and distinct values
- Awareness that the query should return NULL when no second salary exists
- How to generalize to the Nth highest salary
Common Mistakes
- Using LIMIT 1 OFFSET 1, which returns a duplicate top salary when ties exist
- Using ROW_NUMBER, which breaks ties arbitrarily and can miss the true second value
- Forgetting DISTINCT and returning a repeated maximum as the second highest
- Assuming a second salary always exists and not handling NULL
- Confusing RANK (which skips numbers after ties) with DENSE_RANK
Best Answer (HR Friendly)
“You find the highest salary, ignore it, and then take the highest of what is left — that gives you the second highest. In SQL you can do this with a subquery that excludes the maximum, or with a ranking function that labels salaries and you pick the one ranked second.”
Code Example
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);SELECT DISTINCT salary AS second_highest
FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 2;Follow-up Questions
- How would you generalize this to find the Nth highest salary?
- Why is DENSE_RANK better than ROW_NUMBER here?
- What does your query return when there is no second salary?
- How would you find the second highest salary per department?
- What are the trade-offs between the subquery and window-function approaches?
MCQ Practice
1. Which window function correctly returns the second highest distinct salary at rank 2?
DENSE_RANK assigns rank 2 to the next distinct salary after the maximum without skipping numbers or breaking ties arbitrarily.
2. What does SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) return?
It excludes the maximum salary and takes the maximum of the remaining rows, giving the second highest distinct value.
3. Why is LIMIT 1 OFFSET 1 unreliable for the second highest salary?
If multiple employees share the top salary, OFFSET 1 returns another row with the same maximum instead of the second distinct value.
Flash Cards
Second highest salary via subquery? — SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees).
Second highest salary via window function? — Rank salaries with DENSE_RANK() OVER (ORDER BY salary DESC) and select rows where the rank equals 2.
Why DENSE_RANK over ROW_NUMBER? — DENSE_RANK handles ties and does not skip positions, so rank 2 is the true second distinct salary.
Nth highest salary? — Use DENSE_RANK and filter WHERE rnk = N, changing N to get any position.