How to find third or nth maximum salary from salary table?

Question

How to find third or nth maximum salary from salary table(EmpID,EmpName,EmpSalary) in Optimized way?

Answer 1

If you want a single row then use ROW_NUMBER or DENSE_RANK(for all related rows):

WITH CTE AS

(

    SELECT EmpID, EmpName, EmpSalary,

           RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)

    FROM dbo.Salary

)

SELECT EmpID, EmpName, EmpSalary

FROM CTE

WHERE RN = @NthRow