How do you approach SQL: Nth Highest Salary (LeetCode 177)?

DISTINCT then OFFSET / FETCH or LIMIT.

What are common mistakes on SQL: Nth Highest Salary (LeetCode 177)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Nth Highest Salary (LeetCode 177)

MediumDatabases

Description

This problem is paper-only - see explanation for the canonical SQL solution. (LeetCode 177 classic.)

Given Employee(id, salary), return the Nth highest distinct salary, or null if it doesn't exist.

For the auto-grader: implement nthHighestSalary(employees, n). Return the salary number, or null.

Example 1

Input: employees = [{id:1,salary:100},{id:2,salary:200},{id:3,salary:300}], n = 2

Output: 200

Example 2

Input: employees = [{id:1,salary:100}], n = 2

Output: null

Constraints

- 1 <= n <= 100 - 0 <= employees.length <= 10^4

SQL: Nth Highest Salary (LeetCode 177): approach, complexity and pitfalls

A medium sql problem, graded against 7 test cases (5 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

If you are stuck

Show the 2-step hint ladder

DISTINCT then OFFSET / FETCH or LIMIT.
Or DENSE_RANK() OVER (ORDER BY salary DESC) and pick rank = n.

The optimal approach

Show the approach: Canonical SQL (Postgres / MySQL):

```sql

-- LIMIT / OFFSET form

SELECT DISTINCT salary

FROM Employee

ORDER BY salary DESC

OFFSET (n - 1) ROWS FETCH NEXT 1 ROW ONLY;

-- Postgres / SQL Server. MySQL: LIMIT 1 OFFSET (n - 1).

-- Window-function form (engine-portable)

WITH ranked AS (

SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk

FROM Employee

)

SELECT MAX(salary) FROM ranked WHERE rnk = n;

```

Why `DENSE_RANK`: ties at the top should not push the "Nth distinct" result down. `DENSE_RANK` gives 1, 1, 2, 3 - perfect for "distinct value rank". `ROW_NUMBER` would give 1, 2, 3, 4 and skip the duplicate, which is wrong for this question.

LeetCode-specific gotcha: the harness expects NULL when N is out of range, so use `MAX()` (returns NULL on empty group) instead of `SELECT salary ... LIMIT 1` which returns no rows.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.
Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.

← Back to problems

SQL: Nth Highest Salary (LeetCode 177)

MediumDatabases

Description

This problem is paper-only - see explanation for the canonical SQL solution. (LeetCode 177 classic.)

Given Employee(id, salary), return the Nth highest distinct salary, or null if it doesn't exist.

For the auto-grader: implement nthHighestSalary(employees, n). Return the salary number, or null.

Example 1

Input: employees = [{id:1,salary:100},{id:2,salary:200},{id:3,salary:300}], n = 2

Output: 200

Example 2

Input: employees = [{id:1,salary:100}], n = 2

Output: null

Constraints

- 1 <= n <= 100 - 0 <= employees.length <= 10^4

SQL: Nth Highest Salary (LeetCode 177): approach, complexity and pitfalls

A medium sql problem, graded against 7 test cases (5 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

If you are stuck

Show the 2-step hint ladder

DISTINCT then OFFSET / FETCH or LIMIT.
Or DENSE_RANK() OVER (ORDER BY salary DESC) and pick rank = n.

The optimal approach

Show the approach: Canonical SQL (Postgres / MySQL):

```sql

-- LIMIT / OFFSET form

SELECT DISTINCT salary

FROM Employee

ORDER BY salary DESC

OFFSET (n - 1) ROWS FETCH NEXT 1 ROW ONLY;

-- Postgres / SQL Server. MySQL: LIMIT 1 OFFSET (n - 1).

-- Window-function form (engine-portable)

WITH ranked AS (

SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk

FROM Employee

)

SELECT MAX(salary) FROM ranked WHERE rnk = n;

```

LeetCode-specific gotcha: the harness expects NULL when N is out of range, so use `MAX()` (returns NULL on empty group) instead of `SELECT salary ... LIMIT 1` which returns no rows.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.
Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.