Why is my Nth Highest Salary SQL Query Syntax Wrong?

Question

I know there are a ton of questions about nth highest salary, but I don't get why my syntax is wrong. Compilers show that there's an error at the N-1); part but I don't see why that's wrong. Thanks!

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1, OFFSET N-1
  );
END

I don't usually use the `OFFSET` form, instead using `LIMIT offset, count`; but if you use it, I think you don't use a `,`. Also, MySQL can be finicky about where it allows you to use a variable for a limit/offset. — Uueerdo, Aug 18 '16 at 18:13
Does this help: http://stackoverflow.com/questions/2875238/passing-limit-as-parameters-to-mysql-sproc — sgeddes, Aug 18 '16 at 18:25

score 1 · Answer 1 · answered Aug 19 '16 at 01:18

I am going to ignore the fact that depending on your data, the query as you've written it, may not return the results you expect. Instead, I'll focus on fixing the syntax errors:

You need to redefine the default semi colon (;) delimiter to something else so that the semi colon inside the function body doesn't get misinterpreted. (Maybe you're doing that already, but since you're not showing it, I mention it in case)
There shouldn't be a comma separating LIMIT and OFFSET
MySQL seems to expect a single value as the OFFSET parameter. It doesn't seem to like a calculated expression the way you have it. In that case, you just need to calculate the desired value before using it in the SQL statement.

Applying the above, here is what it would look like:

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

Why is my Nth Highest Salary SQL Query Syntax Wrong?

1 Answers1