In this Leetcode Nth Highest Salary problem solution, we need to write a SQL query to get the nth highest salary from the Employee table.
MYSQL solution.
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN DECLARE M INT; SET M = N-1; RETURN ( SELECT IFNULL ((SELECT DISTINCT Salary FROM Employee order by Salary desc limit M,1), null) as getNthHighestSalary ); END
Postgre solution.
drop table if exists employee; create table employee (id int, salary int); insert into employee values (1, 100), (2, 200), (3, 300); create or replace function getnth(n int) returns int as $ranks$ declare ranks int; begin select salary into ranks from employee group by salary order by salary desc limit 1 offset n-1; return ranks; end; $ranks$ LANGUAGE plpgsql; select getnth(3);
Oracle solution.
CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS result NUMBER; BEGIN /* Write your PL/SQL query statement below */ with a as (select salary, dense_rank() over (order by salary desc) as sal_rank from employee) select distinct salary into result from a where sal_rank = n; RETURN result; END;
0 Comments