LeetCode //MySQL - 177. Nth Highest Salary

177. Nth Highest Salary

Table: Employee

±------------±-----+
| Column Name | Type |
±------------±-----+
| id | int |
| salary | int |
±------------±-----+
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.

Write a solution to find the $n^{th}$ highest salary from the Employee table. If there is no $n^{th}$ highest salary, return null.

The result format is in the following example.
 

Example 1:

Input:
Employee table:
±—±-------+
| id | salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
n = 2
Output:
±-----------------------+
| getNthHighestSalary(2) |
±-----------------------+
| 200 |
±-----------------------+

Example 2:

Input:
Employee table:
±—±-------+
| id | salary |
±—±-------+
| 1 | 100 |
±—±-------+
n = 2
Output:
±-----------------------+
| getNthHighestSalary(2) |
±-----------------------+
| null |
±-----------------------+

From: LeetCode
Link: 177. Nth Highest Salary

Solution:

Ideas:

1. Declare the necessary variables:

• nth_highest_salary to store the result.
• offset to calculate the correct offset for the LIMIT clause.

2. Set the offset variable:

• SET offset = N - 1; calculates the correct offset for the LIMIT clause.

3. Select the nth highest salary:

• The subquery selects distinct salaries ordered in descending order.
• LIMIT offset, 1 uses the offset variable to correctly get the nth highest salary.

4. Return the result.

Code:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE nth_highest_salary INT;
  DECLARE offset INT;

  SET offset = N - 1;

  SET nth_highest_salary = (
    SELECT salary
    FROM (
      SELECT DISTINCT salary
      FROM Employee
      ORDER BY salary DESC
      LIMIT offset, 1
    ) AS temp
  );

  RETURN nth_highest_salary;
END;