Table of Contents
Write a SQL query to get the _n_th highest salary from the
+—-+——–+ | 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
+————————+ | getNthHighestSalary(2) | +————————+ | 200 | +————————+
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);
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.
this to know what
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;
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.