sql get Nth hightst salary from table

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.

my answer

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (

      # Write your MySQL query statement below.
    SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT N-1, 1

  );
END

error:

Runtime Error Message:  Line 6: SyntaxError: near '-1, 1 ); END'
Last executed input:    {"headers": {"Employee": ["Id", "Salary"]}, "argument": 1, "rows": {"Employee": [[1, 100]]}}

Look here http://stackoverflow.com/questions/2875238/passing-limit-as-parameters-to-mysql-sproc — Mihai, Jan 11 '15 at 16:32
And this needs to be a function? And what if two salaries are equal? — Strawberry, Jan 11 '15 at 16:32
@Mihai Runtime Error Message: Line 6: SyntaxError: near '(N-1), 1 — BufBills, Jan 11 '15 at 16:33
Juste use `DESC LIMIT N,1` It seems you can't make an arithmetic operation in LIMIT — Olivier, Jan 11 '15 at 16:40

score 0 · Answer 1 · answered Jan 11 '15 at 16:51

DELIMITER $
CREATE FUNCTION salarynth(n1 INT)
  RETURNS INT
   DETERMINISTIC
    BEGIN  
     DECLARE myvar INT;  
     SELECT 
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(group_concat(salary order by id),','), ',', n1),',',-1) INTO myvar FROM t;
     RETURN myvar;
    END$
DELIMITER ;


SELECT `salarynth`('3');

300

sql get Nth hightst salary from table

1 Answers1