Description

Table: Employee

Column NameType
idint
salaryint
  • id is the primary key (column with unique values) for this table.
  • Each row of this table contains information about the salary of an employee.

Problem Statement

Write a solution to find the nth highest salary from the Employee table. If there is no nth highest salary, return null.

The result format is in the following example.

Example 1:

Input:

  • Employee table:
idsalary
1100
2200
3300
  • n = 2

Output:

getNthHighestSalary(2)
200

Example 2:

Input:

  • Employee table:
idsalary
1100
  • n = 2

Output:

getNthHighestSalary(2)
null

Solution

In this case, we need to define a function which can return nth highest salary. This function takes n as an argument. Again, just like Second Highest Salary, we use LIMIT and OFFSET to get the nth highest salary.

1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3  DECLARE M INT;
4  SET M = N - 1;
5  RETURN (
6       SELECT DISTINCT salary FROM Employee ORDER BY salary DESC
7        LIMIT 1 OFFSET M
8  );
9END