Write a query to find the Nth Highest Salary.
Example:
Table: EmployeeID Salary1 4002 2003 3004 500Result: Second highest salary is 400
Mysql
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGINDECLARE esalary INT;SET N=N-1;SELECT DISTINCT Salary into esalaryFROM EmployeeORDER BY Salary DESC LIMIT 1 OFFSET N;return esalary;END
Oracle
CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER ISresult NUMBER;BEGINselect Salary into result from (select Salary, row_number() over (order by Salary desc ) as row_numfrom (select DISTINCT Salary from Employee))where row_num=N;RETURN result;END;
No comments:
Post a Comment