## 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.

```
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.

### 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.

```
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.

## 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.

For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.

## Top comments (2)

Intresting post

Thanks you could check my other post in this series