Nth Salary by DENSE_RANK()

Find nth highest salary by DENSE_RANK()

DECLARE @employees AS TABLE (EmpId INT IDENTITY(1,1), EmpName VARCHAR(20), Salary INT)

DECLARE @nthSalary INT = 3

INSERT INTO @employees(EmpName, Salary)

VALUES ('Sohan', 2000), ('Rohan', 4000), ('Mohan', 3000), ('Ram', 2000), ('Seema', 3000)

/* List all with salary rank */

SELECT *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank FROM @employees

/* List those having required highest salary */

SELECT * FROM

(

      SELECT *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank FROM @employees

)a

WHERE a.SalaryRank = @nthSalary

/* Highest required salary */

SELECT DISTINCT Salary  FROM

(

      SELECT *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank FROM @employees

)a

WHERE a.SalaryRank = @nthSalary