Nth Highest Salary

Nth Highest Salary - Problem

Database Medium

Given the Employee table, write a solution to find the nth highest distinct salary.

Key requirements:

Find the nth highest distinct salary (duplicates are treated as one value)
If there are fewer than n distinct salaries, return null
The parameter n will be passed to your function

Table: Employee

Column Name	Type
id	int
salary	int

id is the primary key for this table. Each row contains information about the salary of an employee.

Table Schema

Employee

Column Name	Type	Description
`id` PK	int	Primary key, unique employee identifier
`salary`	int	Employee salary amount

Primary Key: id

Note: Each row represents one employee with their salary

Input & Output

Example 1 — Finding 2nd Highest Salary

Input Table:

id	salary
1	100
2	200
3	300

Output:

getNthHighestSalary(2)
200

💡 Note:

For n = 2, we need the 2nd highest distinct salary. The distinct salaries in descending order are: 300, 200, 100. The 2nd highest is 200.

Example 2 — With Duplicate Salaries

Input Table:

id	salary
1	100
2	200
3	200

Output:

getNthHighestSalary(2)
100

💡 Note:

For n = 2 with duplicate salaries. Distinct salaries in descending order are: 200, 100. The 2nd highest distinct salary is 100.

Example 3 — Insufficient Distinct Salaries

Input Table:

id	salary
1	100

Output:

getNthHighestSalary(2)

💡 Note:

For n = 2 but only 1 distinct salary exists. Since there are fewer than 2 distinct salaries, the function returns null (empty result).

Constraints

1 ≤ Employee.id ≤ 1000
-10^6 ≤ salary ≤ 10^6
1 ≤ n ≤ 1000
All salary values are integers

Visualization

Tap to expand

Asked in

A Amazon 28 G Google 22 M Microsoft 19 🍎 Apple 15

Use DENSE_RANK() window function with ORDER BY salary DESC to rank distinct salaries, then filter for rank = n. Alternatively, use SELECT DISTINCT with LIMIT OFFSET.

Table Schema

Employee

Column Name	Type	Description
`id` PK	int	Primary key, unique employee identifier
`salary`	int	Employee salary amount

Primary Key: id

Note: Each row represents one employee with their salary

Common Approaches

✓ Subquery with DISTINCT and LIMIT OFFSET

⏱️ Time: O(n log n) Space: O(k)

First get all distinct salaries ordered descending, then use LIMIT with OFFSET to skip the first (n-1) values and get the nth one. This approach is more traditional but equally effective.

Window Function with DENSE_RANK

⏱️ Time: O(n log n) Space: O(n)

DENSE_RANK() assigns consecutive ranks to distinct values, making it perfect for finding the nth highest distinct salary. We rank salaries in descending order and filter for rank = n.

Subquery with DISTINCT and LIMIT OFFSET — Algorithm Steps

Step 1: SELECT DISTINCT salary and ORDER BY salary DESC
Step 2: Use LIMIT 1 OFFSET (n-1) to get the nth row
Step 3: Handle case when fewer than n distinct salaries exist

Visualization

Tap to expand

Step-by-Step Walkthrough

Distinct

Remove duplicate salaries

Sort

Order descending

Offset

Skip to nth position

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting distinct salaries

⚡ Linearithmic

Space Complexity

O(k)

Where k is number of distinct salaries

✓ Linear Space

89.0K Views

High Frequency

~18 min Avg. Time

2.2K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Subquery with DISTINCT and LIMIT OFFSET — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler