Nth Min Salary and Nth MAX Salary

Nth Min Salary and Nth MAX Salary

exec usp_tbl_EmployeeSalary_MinMax NthMIN, NthMAX

Create procedure usp_tbl_EmployeeSalary_MinMax
@NthMIN as int,
@NthMAX as int
as
Begin

declare @min as float;
declare @max as float;
--Nth lowest
set @min = (SELECT salary FROM( SELECT id, salary, Row_Number() OVER(ORDER BY salary ASC) AS lowest FROM tbl_EmployeeSalary ) as x WHERE lowest = @NthMIN)

--Nth highest
Set @max = (SELECT salary FROM ( SELECT id, salary, Row_Number() OVER(ORDER BY salary DESC) AS highest FROM tbl_EmployeeSalary ) as x WHERE highest = @NthMAX)


select ID, Name, Salary,
CASE
WHEN Salary = @min
THEN 'Yes'
else 'No'
END as MinSalary,
CASE
WHEN Salary = @max
THEN 'Yes'
else 'No'
END as MAXSalary
from dbo.tbl_EmployeeSalary
End