Question

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

My Interesting Code

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N - 1;
  RETURN (
      select distinct Salary as getNthHighestSalary
      from Employee 
      order by Salary desc
      limit N, 1
  );
END

My Perspective

For this question, firstly, you can order the salary. Then using “limit” to get the result. By the way, “limit N, 1” means that you pass the first N values, and get the next one value.

This question is not pretty difficult, but it also consumed much time. I was confused by the value of N. In fact, the value of N in “limit N, 1” is N - 1, but how to deal with this? You can use “set N = N - 1” before the “RETURN”.

Also, there are two useful links, and I think they can help you. (chinese version)