Skip to main content
  1. Problem Solving Solutions/

Nth Highest Salary LeetCode Solution

·2 mins
leetcode SQL
Mayukh Datta
Author
Mayukh Datta
Table of Contents

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.