4 Ways to find Nth highest salary in SQL

4 Ways to find Nth highest salary in SQL

SELECT Max(salary) AS Salary FROM   employee WHERE  salary < (SELECT Max(salary) FROM   employee)

Using Max function

SELECT TOP 1 * FROM   (SELECT DISTINCT TOP 2 salary FROM   employee ORDER BY salary DESC) AS t ORDER BY t.salary ASC

Using Inline Query

select top 1 Id,Name,Salary  from ( select Id,Name,Salary, DENSE_RANK() over(order by salary desc) as R from Employee) result where result.R=2

Using Dense_Rank

with tmp_emp as( select Id,Name,Salary, DENSE_RANK() over(order by salary desc) as Rank from Employee)

Using CTA