Write a query to find the second-highest distinct salary?
Interview preparation resource from Gate Smashers.
Use the maximum salary that is less than the overall maximum: SELECT MAX(salary) AS second_highest_salary FROM Employees WHERE salary < (SELECT MAX(salary) FROM Employees); This returns the second-highest distinct salary (NULL if fewer than two distinct salaries exist).
| employee_id | employee_name | department | salary | manager_id |
|---|---|---|---|---|
| 101 | Aarav | Engineering | 95000 | 105 |
| 102 | Meera | Engineering | 72000 | 105 |
| 103 | Kabir | Sales | 65000 | 106 |
| 104 | Isha | Sales | 92000 | 106 |
| 105 | Rohan | Engineering | 90000 | NULL |
| 106 | Neha | Sales | 88000 | NULL |
SELECT MAX(salary) AS second_highest_salary
FROM Employees
WHERE salary < ( SELECT MAX(salary) FROM Employees );Query (direct answer)
Find the second-highest distinct salary by selecting the maximum salary that is strictly less than the overall maximum salary. The query below implements this approach and returns NULL when fewer than two distinct salaries exist.
- Query: See the SQL code block in Content Blocks for the exact statement.
How it works
The solution uses a subquery to determine the overall maximum salary, then the outer query finds the largest salary smaller than that value. This ensures the result is the second-highest distinct salary rather than a tied value equal to the maximum.
- Inner query: Finds the highest salary: (SELECT MAX(salary) FROM Employees).
- Outer query: Finds the maximum salary where salary is less than the value returned by the inner query.
Edge case
If the table contains fewer than two distinct salary values, the WHERE filter excludes all rows and the aggregate MAX returns NULL. That indicates no second-highest distinct salary exists.
- Result when insufficient distinct salaries: NULL
When to prefer DENSE_RANK
Use DENSE_RANK (or similar window functions) when you need to return employee details along with the nth-highest salary or when requesting arbitrary ranks (e.g., 3rd highest). DENSE_RANK handles ties and makes it easy to select rows with a specific rank rather than only the scalar salary value.
- Use-case: Return employee rows or nth-highest values; handle ties explicitly.