### Problem Description –

Write a SQL query to get the *n*^{th} highest salary from the** Employee** table.

For example, given the above Employee table, the *n*^{th} highest salary where ** n = 2 is 200**. If there is no

*n*

^{th}highest salary, then the query should return

`null`

.#### Difficulty Level – Medium

#### Problem Link – https://leetcode.com/problems/nth-highest-salary/

### Solution –

```
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
salary as getNthHighestSalary
FROM (
SELECT
salary,
DENSE_RANK() OVER(ORDER BY salary DESC) as sal_rank
FROM Employee
) t1
WHERE sal_rank = N
LIMIT 1
);
END
```

To rank all the salaries of the employees, we are using the **DENSE_RANK()** function and ordering the salary in descending order, so that the highest salary will get the rank of 1. Then in the outer query, we are using the WHERE statement to select the Nth highest salary and also LIMIT the result to 1 to show only one value. You can also use DISTINCT instead of the limit.

And the reason for not using the **RANK() **function is that if we have duplicate rows, then the **RANK()** function will leave holes in the ranking, which will give you the wrong answer.

```
SELECT salary,
RANK() OVER(ORDER BY salary DESC) as sal_rank
FROM Employee
```

Here, you can see that as we have two salaries of 300, both get the rank of 1 but the second-highest salary gets the rank of 3 as the **RANK() **function leaves holes in the ranking when we have duplicate rows.

But if we use the **DENSE_RANK()** function, we will get the right result.

```
SELECT salary,
DENSE_RANK() OVER(ORDER BY salary DESC) as sal_rank
FROM Employee
```

Here, you can see that the salary of 200 gets the ranking of 2, which is the correct response. When to use which function depends on the problem. Here, using **DENSE_RANK() **will give you the correct result.

There is also a **ROW_NUMBER()** function, which will give a unique rank to each row whether you have duplicate data or not.

```
SELECT salary,
ROW_NUMBER() OVER(ORDER BY salary DESC) as sal_rank
FROM Employee
```

## 2 thoughts