SQL implementation of LeetCode (177. Nth highest salary)

[LeetCode] 177.Nth Highest Salary

Write a SQL query to get the n th 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.

This question is an extension of the previous Second Highest Salary question. Based on the approach of the previous question, we can easily extend it to N. According to the analysis of Solution 1 in Second Highest Salary, we only need to change the 1 after OFFSET to N-1. However, MySQL will report an error, probably because it does not support the operation. Then we can add a SET N = N - 1 in front to change N to N-1 first and then do the same:

Solution 1:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N - 1;
  RETURN (
      SELECT DISTINCT Salary FROM Employee GROUP BY Salary
      ORDER BY Salary DESC LIMIT 1 OFFSET N
  );
END

According to the analysis of Solution 4 in Second Highest Salary, we only need to change 1 to N-1. However, here we support the calculation of N-1. See the code below:

Solution 2:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      SELECT MAX(Salary) FROM Employee E1
      WHERE N - 1 =
      (SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2
      WHERE E2.Salary > E1.Salary)
  );
END

Of course, we can also change the last > to >=, so that we can replace N-1 with N:

Solution 3:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      SELECT MAX(Salary) FROM Employee E1
      WHERE N =
      (SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2
      WHERE E2.Salary >= E1.Salary)
  );
END

Similar topics:

Second Highest Salary

References:

https://leetcode.com/discuss/88875/simple-answer-with-limit-and-offset

https://leetcode.com/discuss/63183/fastest-solution-without-using-order-declaring-variables

This is the end of this article about SQL implementation of LeetCode (177. The Nth highest salary). For more relevant SQL implementation of the Nth highest salary content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!