Fetching Second highest and third highest Salary of employee

Sometimes we get into situation where we need to fetch second and third highest salary of someone rather than highest salary.

To get Second highest Salary of an employee:


select Name,Salary from employee where
Salary = (select MAX(Salary) from employee
WHERE Salary NOT IN (select MAX(Salary) from employee)) ;

OR

select name , salary from employee
order by salary desc
offset 1 rows
Fetch next 1 rows only ;

To get Third highest Salary of employee:


select Name , Salary from Employee where salary in (
SELECT TOP 1 salary FROM (
SELECT TOP 3 salary
FROM Employee
ORDER BY salary DESC) AS emp
ORDER BY salary ASC);

OR

select name , salary from employee
order by salary desc
offset 2 rows
Fetch next 1 rows only ;