Previously I have written a post on Find out Nth highest value in SQL but that is not a good solution. You should try to avoid using “In” clause in your queries. The “In” clause uses lot of memory and should be avoided. So this post tells how to do the same using “Joins” rather than “In” clause.
The below query will fetch the 3rd highest salary,
select top 1 e.firstname, e.lastname, e.salary from employees e left outer join (select top 2 employeeid from employees order by salary desc) b on e.employeeid=b.employeeid where b.employeeid is null order by e.salary desc
If you want to fetch some other number change the “2” in the following line in the above query
(select top 2 employeeid from employees order by salary desc) b
If you know a better way, please let us know.
Hope this helps