Write a SQL query to get the _n_th highest salary from the `Employee`

table.

+—-+——–+ | Id | Salary | +—-+——–+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +—-+——–+

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`

.

+————————+ | getNthHighestSalary(2) | +————————+ | 200 | +————————+

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

## Solution using correlated subquery #

CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS result NUMBER; BEGIN select distinct(A.Salary) into result from Employee A where (N-1) = (select count(distinct B.salary) from Employee B where B.Salary > A.Salary);

```
RETURN result;
```

END;

The highest salary means there is no salary greater than it. The second highest means one salary is greater than it, third-highest means two salaries are greater than it and so on. We can also put it this way that N - 1 salaries are greater than the Nth highest salary. So, the idea here is to find the Nth highest salary using correlated subquery.

This is a generic solution that works in all databases but is slow because the inner row runs for every row processed by the outer query.

## Solution using `row_number()`

#

Read
this to know what `row_number()`

does.

CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS result NUMBER; BEGIN select Salary into result from (select Salary, row_number() over (order by Salary desc) as row_num from Employee group by Salary) where row_num = N;

```
RETURN result;
```

END;

We fetch the Nth highest salary from a newly created temporary table. The temporary table has grouped the salaries to avoid duplicates in the result query set, ordered them in descending order, and added a new column that has row numbers assigned to each row.

This solution is efficient and faster than the solution using correlated subquery.