Nth Highest Salary | LeetCode | MSSQL
Tags: codenewbie, beginners, leetcode, mssql
Published: 2023-06-11 15:57:47

## Problem Statement

In this post, we'll be addressing a common SQL problem: finding the nth highest salary from a table of employees. If there is no nth highest salary, the query should return null. This type of problem often shows up in coding assessments and job interviews, so let's delve into the solution. The structure of the `Employee` table is as follows:

| Column Name | Type |
| --- | --- |
| id | int |
| salary | int |

The `id` is the primary key, and each row contains salary information for an employee. 

The objective is to write an SQL query that can return the nth highest salary.

Example 1:

| id | salary |
| --- | --- |
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |

With n = 2, the output would be:

| getNthHighestSalary(2) |
| --- |
| 200 |

Example 2:

| id | salary |
| --- | --- |
| 1 | 100 |

With n = 2, the output would be:

| getNthHighestSalary(2) |
| --- |
| null |

## Approaches to the Solution

We'll explore two approaches that involve creating a SQL function to solve the problem. Each method uses different concepts and techniques in SQL. We'll explain each one and highlight their strengths and weaknesses. 

### 1. Using a Stored Function with DENSE_RANK()

The first solution creates a stored function `getNthHighestSalary` which uses the `DENSE_RANK()` window function to rank the salaries in descending order. 

```sql
CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
    RETURN (
        SELECT s.salary
        FROM
        (
            SELECT DISTINCT
                salary,
                DENSE_RANK() OVER (ORDER BY salary DESC) [drank]
            FROM Employee
        ) [s]
        WHERE s.drank = @N
    );
END
```
This approach provides a clear and logical solution. However, it is not the most performant, with a runtime of 816ms, beating only 29.26% of LeetCode submissions.

![Runtime Screenshot](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xmnfukizyflk6tl0jesk.png)

### 2. Using a Stored Function with ROW_NUMBER()

The second solution also creates a stored function, but instead uses the `ROW_NUMBER()` window function to rank the salaries in descending order. 

```sql
CREATE FUNCTION getNthHighestSalary(@N INT) RETURNS INT AS
BEGIN
    RETURN (
        SELECT salary
        FROM (
            SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
            FROM (
                SELECT DISTINCT salary
                FROM Employee
            ) AS temp
        ) AS ranked_salaries
        WHERE rn = @N
    );
END
```

This approach performs slightly better, with a runtime of 704ms, beating 55.52% of LeetCode submissions.

![Runtime Screenshot](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4sk7ru5x96laqvpoa4tg.png)

## Conclusion

These solutions demonstrate how to use stored functions and window functions to solve a common problem in SQL. While the two approaches are similar, using `ROW_NUMBER()` slightly outperforms `DENSE_RANK()` on LeetCode. 

However, the best solution can vary depending on the RDBMS and the specific dataset. Therefore, it's always a good idea to test different methods in your specific environment.

You can find the original problem at [LeetCode](https://leetcode.com/problems/nth-highest-salary/description/).

For more insightful solutions and tech-related content, feel free to connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% embed https://beacons.ai/ranggakd %}