177_Nth Highest Salary

Write a SQL query to get thenthhighest salary from theEmployeetable.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, thenthhighest salary wheren= 2 is200. If there is nonthhighest salary, then the query should returnnull.

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

Solution

MySQL can only take numeric constants in the LIMIT syntax.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = N-1;  -- need to have M
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary
      FROM Employee
      ORDER BY Salary DESC
      LIMIT 1 OFFSET M  -- N-1 won't work here
  );
END