I need to fetch all details of employee with third highest salary in the most efficient way.
FROM (SELECT * TOP (3) Salary
ORDER BY Salary)
You can use Rownumber:Here i partitioned by empid to avoid ties
;With cte as ( select *,row_number() over (partition by empid order by salary desc) as rownum from table ) select * from cte where rownum=3
if you want to use your query:
SELECT MIN(Salary FROM ( SELECT TOP (3) Salary FROM Employees ORDER BY Salary )b