Nth Highest Salary Problem


Description

LeetCode Problem 177.

Write a SQL query to get the nth highest salary from the Employee table.

1
2
3
4
5
6
7
+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

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 null.

1
2
3
4
5
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+


MySQL Solution

1
2
3
4
5
6
7
8
9
10
11
12
create function getNthHighestSalary(N int) returns int
begin
  return (
      select distinct salary as 'getNthHighestSalary(2)'
       from  (
          select salary,
                dense_rank() over(order by salary desc) as rnk
          from employee
      )  sal_rnk
      where rnk = n      
  );
end




Related Posts

Nth Highest Salary Problem

LeetCode 177. Write a SQL query to get the nth...

Rank Scores Problem

LeetCode 178. Write a SQL query to rank scores. If...

Consecutive Numbers Problem

LeetCode 180. Write an SQL query to find all numbers...

Second Highest Salary Problem

LeetCode 176. Given the Employee table, write a SQL query...

Employees Earning More Than Their Managers Problem

LeetCode 181. Given the Employee table, write a SQL query...

Combine Two Tables Problem

LeetCode 175. Write a SQL query for a report that...