Problem Description –
Write a SQL query to get the nth highest salary from the
For example, given the above Employee table, the nth highest salary where n = 2 is
200. If there is no nth highest salary, then the query should return
Difficulty Level – Medium
Problem Link – https://leetcode.com/problems/nth-highest-salary/
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