MediumDatabaseUpdated Sep 2026

Nth Highest Salary

Asked at Atlassian

Difficulty

Medium

Time

O(m log m)

Space

O(m)

Pattern

Database

Companies

1

Problem

You are given a table Employee with columns id (unique) and salary. Write a function getNthHighestSalary(N) that returns the N-th highest distinct salary in the table.

Two rules matter. First, duplicates are ignored: if two employees earn the same amount, that amount counts as one salary level, not two. Second, if the table has fewer than N distinct salaries, the function must return null instead of an error or an empty result.

The function takes N as an input parameter, so your query must work for any value of N, not just a fixed one.

Distinct salaries, ranked high to low

distinct salaries (desc)300200100012N=2The 2nd position holds 200, so the answer is 200.
Duplicates collapse into one level, then we count down to the N-th level.

Constraints

  • ●id is the primary key of the Employee table
  • ●1 <= N <= 100 (N is passed as a function parameter)
  • ●Salaries are integers and may repeat across rows
  • ●Return null when fewer than N distinct salaries exist

Examples

Example 1
InputEmployee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ n = 2
Output+------------------------+ | getNthHighestSalary(2) | +------------------------+ | 200 | +------------------------+
WhyThe distinct salaries sorted high to low are 300, 200, 100. The 2nd one is 200.
Example 2
InputEmployee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | +----+--------+ n = 2
Output+------------------------+ | getNthHighestSalary(2) | +------------------------+ | null | +------------------------+
WhyThere is only one distinct salary (100), so there is no 2nd highest salary. The function returns null.

Asked At

1 companies
CompanyDifficulty
AtlassianMediumView all Atlassian questions →

How to Think About It

1

Use DISTINCT to handle duplicate salary values

2

Apply OFFSET (n-1) to skip the first n-1 highest salaries

3

Use LIMIT 1 to return only the nth highest salary

4

Return NULL when the query returns no rows using IFNULL or subquery

5

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.

Sort, skip N-1 rows, take one

SELECT DISTINCT salaryORDER BY salary DESCOFFSET N-1LIMIT 1Skip the top N-1 salaries, then keep only the nextone.SET N = N - 1;... LIMIT 1 OFFSET NIFNULL(result, NULL)
ORDER BY salary DESC plus DISTINCT gives the ranked list; OFFSET N-1 jumps to the N-th row.
ComplexityTime O(m log m)Space O(m)Sorting the m distinct salaries dominates the cost; the OFFSET/LIMIT scan itself is cheap.

What Trips People Up in Real Interviews

1

Clarify whether duplicate salaries should count as one or multiple ranks

2

Explain why DISTINCT is necessary to avoid counting duplicates

3

Discuss OFFSET behavior when fewer than n unique salaries exist

4

Mention that LIMIT 1 with OFFSET (n-1) is cleanest for single result

5

Consider edge cases: n=1, n larger than number of distinct salaries

Solution Code

O(m log m) time
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
    )
  );
END

Pro at DSA?

Test your skills with a real FAANG-style mock interview.

Start a Mock Interview →

Frequently 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 runs in O(m log m) time and O(m) space. Scroll up to "Optimal Approach" for the step-by-step walkthrough, diagrams, and code in Python, C++ and Java.

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