Nth Highest Salary
Asked at Atlassian
Problem
Write a SQL query to find the nth highest salary from an Employee table. If there is no nth highest salary, return null. The solution must handle duplicate salaries and use LIMIT/OFFSET or window functions.
Asked At
| Company | Difficulty | |
|---|---|---|
| Atlassian | MEDIUM | View all Atlassian questions → |
How to Think About It
Use DISTINCT to handle duplicate salary values
Apply OFFSET (n-1) to skip the first n-1 highest salaries
Use LIMIT 1 to return only the nth highest salary
Return NULL when the query returns no rows using IFNULL or subquery
Consider using DENSE_RANK() window function as alternative
Optimal Approach
Use a subquery with DISTINCT salaries ordered in descending order. Apply OFFSET (n-1) to skip the top n-1 salaries, then LIMIT 1 to get the nth. Wrap in IFNULL to return NULL when no result exists. The function approach allows parameterized n values for flexibility.
What Trips People Up in Real Interviews
Clarify whether duplicate salaries should count as one or multiple ranks
Explain why DISTINCT is necessary to avoid counting duplicates
Discuss OFFSET behavior when fewer than n unique salaries exist
Mention that LIMIT 1 with OFFSET (n-1) is cleanest for single result
Consider edge cases: n=1, n larger than number of distinct salaries
Solution Code
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N - 1;
RETURN (
SELECT IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET N),
NULL
)
);
ENDFrequently Asked Questions
What is the Nth Highest Salary problem?
Write a SQL query to find the nth highest salary from an Employee table. If there is no nth highest salary, return null. The solution must handle duplicate salaries and use LIMIT/OFFSET or window functions.
How do you solve Nth 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 Nth Highest Salary?
Nth Highest Salary is asked at Atlassian. It is a medium difficulty problem.
What are common mistakes on Nth Highest Salary?
- Clarify whether duplicate salaries should count as one or multiple ranks
- Explain why DISTINCT is necessary to avoid counting duplicates
- Discuss OFFSET behavior when fewer than n unique salaries exist
- Mention that LIMIT 1 with OFFSET (n-1) is cleanest for single result
- Consider edge cases: n=1, n larger than number of distinct salaries