Second Highest Salary
Asked at Adobe
Difficulty
Medium
Time
O(n)
Space
O(1)
Pattern
Database
Companies
1
Problem
You are given a table called Employee with two columns: id (an integer primary key) and salary (an integer). Each row describes one employee and their salary.
Write a SQL query that returns the second highest distinct salary in the table. "Distinct" means repeated salary values count only once — if two employees both earn 300, that is a single salary value, not two.
The result must be a single column named SecondHighestSalary with one row. If there is no second highest distinct salary (for example, the table is empty or every employee has the same salary), return null instead of an empty result.
Pick the second distinct salary
Constraints
- ●
id is the primary key of the Employee table, so every id is unique - ●
1 <= number of rows <= 10^5 - ●
0 <= salary <= 10^5 - ●
The answer column must be named SecondHighestSalary - ●
Return exactly one row; use null when there is no second highest distinct salary
Examples
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------++---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------++---------------------+
| SecondHighestSalary |
+---------------------+
| null |
+---------------------+Asked At
1 companies| 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.
Max below the overall max
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
O(n) timeSELECT 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 runs in O(n) time and O(1) space. Scroll up to "Optimal Approach" for the step-by-step walkthrough, diagrams, and code in Python, C++ and Java.
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.