LeetCode 177: Nth Highest Salary Solution in SQL Explained

Finding the Nth highest salary in a table might feel like ranking employees in a payroll competition, and LeetCode 177: Nth Highest Salary is a medium-level challenge that makes it engaging! Given an Employee table with columns id and salary, you need to write an SQL function getNthHighestSalary(N) to return the Nth highest salary, or null if it doesn’t exist. In this blog, we’ll solve it with SQL, exploring two solutions—Subquery with LIMIT and OFFSET (our best solution) and Dense Rank with Subquery (a practical alternative). With step-by-step examples, detailed query breakdowns, and tips, you’ll master this problem. While this is an SQL challenge, you can explore Python basics at Python Basics for related coding skills. Let’s rank those salaries!

Problem Statement

In LeetCode 177, you’re given an Employee table:

id (int, primary key)
salary (int)

Your task is to write an SQL function getNthHighestSalary(N) that takes an integer N and returns the Nth highest salary from the table. If there is no Nth highest salary (e.g., fewer than N distinct salaries), return null. This generalizes LeetCode 176: Second Highest Salary for any N, differing from table joining like LeetCode 175: Combine Two Tables.

Constraints

Table has at least 1 row.
salary values are non-negative integers.
Duplicate salaries may exist.
1 ≤ N ≤ 10^5

Example

Let’s see some cases:

Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Input: getNthHighestSalary(2)
Output:
+-----------+
| getNthHighestSalary |
+-----------+
| 200       |
+-----------+
Explanation: 300 (1st), 200 (2nd), 100 (3rd).

Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
Input: getNthHighestSalary(2)
Output:
+-----------+
| getNthHighestSalary |
+-----------+
| null      |
+-----------+
Explanation: Only one salary, no 2nd highest.

Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 100    |
| 3  | 200    |
+----+--------+
Input: getNthHighestSalary(2)
Output:
+-----------+
| getNthHighestSalary |
+-----------+
| 100       |
+-----------+
Explanation: 200 (1st), 100 (2nd).

These examples show we’re finding the Nth highest distinct salary.

Understanding the Problem

How do you solve LeetCode 177: Nth Highest Salary in SQL? We need a function to:

Accept an integer N.
Return the Nth highest salary from Employee.
Return null if fewer than N distinct salaries exist.

For [100,200,300], N=2 returns 200 (2nd highest). For [100], N=2 returns null. For [100,100,200], N=2 returns 100. We must handle duplicates (distinct salaries) and edge cases (insufficient rows), ideally in a concise query. This isn’t a Python coding task like LeetCode 174: Dungeon Game; it’s about SQL ranking with parameterization. We’ll use: 1. Subquery with LIMIT and OFFSET: Efficient, flexible—our best solution. 2. Dense Rank with Subquery: Alternative approach.

Let’s dive into the best solution.

Best Solution: Subquery with LIMIT and OFFSET Approach

Explanation

Subquery with LIMIT and OFFSET finds the Nth highest salary by:

Creating a function getNthHighestSalary that takes N.
Using a subquery to:

Select distinct salaries, order descending.
Apply LIMIT 1 OFFSET N-1 to get the Nth highest.

Wrapping in IFNULL to return null if no result.

This ensures flexibility for any N, runs efficiently (O(n log n) or better with indexing), and handles edge cases cleanly.

For [100,200,300], N=2:

Subquery: SELECT DISTINCT salary ORDER BY salary DESC LIMIT 1 OFFSET 1 → 200.
Wrap: IFNULL(200, NULL) → 200.

Step-by-Step Example

Example 1: Employee = [100,200,300], N = 2

Goal: Return 200.

Step 1: Subquery for Nth highest.

SELECT DISTINCT salary FROM Employee → [100,200,300].
ORDER BY salary DESC → [300,200,100].
LIMIT 1 OFFSET 1 (N-1=1) → Skip 300, take 200.

Step 2: Wrap with IFNULL.

IFNULL(200, NULL) → 200.

Finish: Return 200.

Example 2: Employee = [100], N = 2

Goal: Return null.

Step 1: Subquery.

DISTINCT salary → [100].
ORDER BY DESC → [100].
LIMIT 1 OFFSET 1 → Empty result.

Step 2: Wrap.

IFNULL(NULL, NULL) → null.

Finish: Return null.

Example 3: Employee = [100,100,200], N = 2

Goal: Return 100.

Step 1: Subquery.

DISTINCT salary → [100,200].
ORDER BY DESC → [200,100].
LIMIT 1 OFFSET 1 → 100.

Step 2: Wrap.

IFNULL(100, NULL) → 100.

Finish: Return 100.

How the Code Works (Subquery with LIMIT and OFFSET) – Detailed Line-by-Line Explanation

Here’s the SQL query with a thorough breakdown:

-- Line 1: Create function
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    -- Line 2: Return query with IFNULL
    RETURN (
        SELECT IFNULL(
            -- Line 3: Subquery for Nth highest
            (SELECT DISTINCT salary
             FROM Employee
             ORDER BY salary DESC
             LIMIT 1 OFFSET N-1),
            -- Get Nth highest (e.g., 200 for N=2 from [300,200,100])

            -- Line 4: Null if no result
            NULL
            -- Default if fewer than N salaries (e.g., for [100], N=2)
        )
    );
END

This detailed breakdown clarifies how the subquery with LIMIT and OFFSET efficiently finds the Nth highest salary.

Alternative: Dense Rank with Subquery Approach

Explanation

Dense Rank with Subquery uses ranking to find the Nth highest:

Create a function getNthHighestSalary.
Use DENSE_RANK() to assign ranks to distinct salaries.
Select salary where rank = N, wrap with IFNULL.

It’s a practical alternative, O(n log n) time with indexing, but requires window function support (e.g., MySQL 8.0+), and may be less intuitive than LIMIT/OFFSET, still O(1) space beyond result.

For [100,200,300], N=2:

Ranks: 300(1), 200(2), 100(3).
Select rank=2 → 200.

Step-by-Step Example (Alternative)

For [100,200,300], N=2:

Step 1: Subquery with DENSE_RANK.

DISTINCT salary → [100,200,300].
DENSE_RANK() OVER (ORDER BY salary DESC) → [300:1, 200:2, 100:3].
WHERE rank = 2 → 200.

Step 2: Wrap.

IFNULL(200, NULL) → 200.

Finish: Return 200.

How the Code Works (Dense Rank)

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    RETURN (
        SELECT IFNULL(
            (SELECT salary
             FROM (
                 SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
                 FROM Employee
             ) t
             WHERE rnk = N
             LIMIT 1),
            NULL
        )
    );
END

Complexity

Subquery with LIMIT and OFFSET:

Time: O(n log n) – sorting with limit (optimized with indexing).
Space: O(1) – minimal extra space.

Dense Rank with Subquery:

Time: O(n log n) – ranking operation (database-dependent).
Space: O(1) – minimal extra space.

Efficiency Notes

Subquery with LIMIT and OFFSET is the best solution with O(n log n) time (often optimized), O(1) space, offering simplicity and broad compatibility—Dense Rank with Subquery matches time complexity but requires window function support (less portable) and may be less intuitive, though equally space-efficient.

Key Insights

LIMIT OFFSET: Flexible ranking.
DENSE_RANK: Built-in ranking.
IFNULL: Null handling.

Additional Example

Employee:
| 1 | 400 |
| 2 | 400 |
| 3 | 300 |
N = 2
Output:
| 300 |
Explanation: 400 (1st), 300 (2nd).

Edge Cases

Single Salary: [100], N=2 → null.
Duplicates: [100,100], N=2 → null.
N > Distinct: [100,200], N=3 → null.

Both solutions handle these well.

Final Thoughts

LeetCode 177: Nth Highest Salary in SQL is a versatile ranking challenge. The Subquery with LIMIT and OFFSET solution excels with its efficiency and clarity, while Dense Rank with Subquery offers a modern alternative. Want more SQL? Try LeetCode 176: Second Highest Salary for a simpler case or explore Python Basics for coding skills. Ready to practice? Solve LeetCode 177 on LeetCode with [100,200,300] and N=2, aiming for 200—test your skills now!