Runtime error when using N-1 in limit clause of select query when N=0

Question

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

this query gives exception when M=0 as it becomes -1 however when I write it like

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

Please explain the difference between the two statements. When I set M= N-1 , m will also be negative when N=0 it gives following exception

Line 6: SyntaxError: near '-1,1
);
END'

Answer 1

I'm arriving late on this, but I struggled with this problem too. Spent hours debugging this. You could always check for this condition in the where clause. Check the query below.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = -1;
 RETURN (
    select distinct salary from employee where M > 0 order by salary desc limit M,1
 );
END

The parameters in limit are confusing as well. In some cases offset is not supported. There's a simple example for this.

limit a,b;

We can read the above as

start from a and stop after b number of entries.

Cheers!

Answer 2

I'm not sure if this helps. But this looks like a problem from Leet Code (https://leetcode.com/problems/nth-highest-salary). I ran the below and it worked for me.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
RETURN (
   select distinct
       Salary
   from 
       Employee
   Order by 1 desc
   limit M, 1

   );
   END

And here's the submission result:

Hope this helps!

Answer 3

My answer Thanks for @George Hayward

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set N = N-1;

  RETURN (
      # Write your MySQL query statement below.
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT N,1),
    NULL)
  );
END

Answer 4

Don't forget to group the salaries.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N - 1;
  RETURN (
      select salary from Employee group by salary order by salary desc limit 1 offset N
  );
END

So if you have two salaries of 100 and you are looking for 2nd highest, the answer should be NULL.

Answer 5

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