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.