Demo Script
IF OBJECT_ID('Tempdb..#Employee') IS NOT NULL
DROP TABLE #Employee;
CREATE TABLE #Employee
(EmployeeName VARCHAR(75), Salary MONEY);
INSERT INTO #Employee
VALUES
('A', 10000)
,('B', 20000)
,('C', 30000)
,('D', 30000)
,('E', 20000)
,('F', 40000)
,('G', 5000);
SELECT * FROM #Employee;
Finding Nth Highest Salary
--Nth Highest Salary
DECLARE @Nth_Highest_Salary INT;
SET @Nth_Highest_Salary=3;
SELECT Salary AS Nth_Highest_Salary FROM
(
SELECT DISTINCT Salary,DENSE_RANK() OVER(ORDER BY Salary DESC) AS R_NO FROM #Employee
) A
WHERE R_NO=@Nth_Highest_Salary;
Finding Nth Lowest Salary
--Nth Lowest Salary
DECLARE @Nth_Lowest_Salary INT;
SET @Nth_Lowest_Salary=2;
SELECT Salary AS Nth_Lowest_Salary FROM
(
SELECT DISTINCT Salary,DENSE_RANK() OVER(ORDER BY Salary ASC) AS R_NO
FROM #Employee
) A
WHERE R_NO=@Nth_Lowest_Salary;