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.

Leetcode Nth Highest Salary problem solution


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;