Question

Formatted question description: https://leetcode.ca/all/177.html

177	Nth Highest Salary

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

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

 +------------------------+
 | getNthHighestSalary(2) |
 +------------------------+
 | 200                    |
 +------------------------+

Algorithm

Change the 1 after OFFSET to N-1, but then MySQL will report an error and it is estimated that the operation is not supported. Then we can add a SET N = N-1 in front.

Code

SQL

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE M INT;
  SET M=N-1; # index issue
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary
      FROM Employee
      ORDER BY Salary DESC
      LIMIT 1
      OFFSET M
  );
END

All Problems

All Solutions