Reputation: 635
I have a table like this:
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
I am writing a function with mySQL to get the n th largest value in Salary. Here is the function:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
select DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 offset (N - 1)
#FETCH NEXT 1 ROWS ONLY
);
END
But I got the an error near (N-1).
if I change (N-1) to 1 :
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
select DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 offset 1
#FETCH NEXT 1 ROWS ONLY
);
END
It runs correctly. So the question is how to reference input in SQL function? It seems it can be directly called as an argument as we do in other languages.
Upvotes: 0
Views: 822
Reputation: 635
It seems I need to declare and set a variable before reference it in SQL query:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
RETURN (
# Write your MySQL query statement below.
select DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET M
#FETCH NEXT 1 ROWS ONLY
);
END
Upvotes: 1
Reputation: 42854
LIMIT argument cannot be a variable. Use prepared statement - in it the LIMIT parameter may be taken from a variable. But dynamic SQL is not allowed in the function - use stored procedure:
CREATE PROCEDURE getNthHighestSalary(N INT)
BEGIN
SET @sql := 'SELECT DISTINCT Salary
INTO @output
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET ?';
PREPARE stmt FROM @sql;
SET @output := N-1;
EXECUTE stmt USING @output;
DROP PREPARE stmt;
SELECT @output;
END
Upvotes: 1