Second Highest Salary
Asked at Adobe
Problem
Write a SQL query to find the second highest distinct salary from the Employee table. If there is no second highest salary, the query should return null.
Asked At
| Company | Difficulty | |
|---|---|---|
| Adobe | MEDIUM | View all Adobe questions → |
How to Think About It
Sort all salaries in descending order and pick the second distinct value.
Use a subquery to find the maximum salary, then find the maximum salary less than that.
Use LIMIT and OFFSET to skip the first row after ordering.
Use the DISTINCT keyword to handle duplicate salary values.
Combine a subquery with IS NULL check to return null when only one salary exists.
Optimal Approach
Use a subquery that finds the maximum salary strictly less than the overall maximum salary. First compute MAX(salary) from the table, then compute MAX(salary) from the table where salary is less than that value. This naturally returns null if no second highest salary exists. An alternative approach uses ORDER BY salary DESC DISTINCT with LIMIT 1 OFFSET 1, which is concise but requires DISTINCT support.
What Trips People Up in Real Interviews
Clarify whether duplicate salaries should be counted as one or two.
Handle the edge case where fewer than two distinct salaries exist by returning null.
Always use DISTINCT if the problem says "second highest" implies unique values.
Avoid using TOP or FETCH in databases that prefer LIMIT syntax unless specified.
Test your query mentally with a single-row table to confirm null behavior.
Solution Code
SELECT MAX(salary) AS SecondHighestSalary FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee)
Frequently Asked Questions
What is the Second Highest Salary problem?
Write a SQL query to find the second highest distinct salary from the Employee table. If there is no second highest salary, the query should return null.
How do you solve Second Highest Salary?
The optimal approach is described in detail above, including step-by-step walkthroughs, complexity analysis, and solution code in Python. Scroll up to the "Optimal Approach" section.
What companies ask Second Highest Salary?
Second Highest Salary is asked at Adobe. It is a medium difficulty problem.
What are common mistakes on Second Highest Salary?
- Clarify whether duplicate salaries should be counted as one or two.
- Handle the edge case where fewer than two distinct salaries exist by returning null.
- Always use DISTINCT if the problem says "second highest" implies unique values.
- Avoid using TOP or FETCH in databases that prefer LIMIT syntax unless specified.
- Test your query mentally with a single-row table to confirm null behavior.