Write an SQL query to report the nth highest
salary from the Employee table. If there is no nth highest salary, the query
should report null.
table name: employee
Solution:
CREATE FUNCTION getNthHighestSalary(@N INT)
RETURNS INT AS
BEGIN
declare @result int;
with cte as
(
select dense_rank() over (order by salary desc) as rnk,salary from Employee1
)
select @result= salary from cte where rnk = @N
return @result;
end
SQL Script:
Create table Employee (Id int, Salary int)
insert into Employee (id, salary) values ('1', '100')
insert into Employee (id, salary) values ('2', '200')
insert into Employee (id, salary) values ('3', '300')