How to find third or n maximum salary from salary table

Question

How can I efficiently retrieve the third or nth highest salary in the salary table (EmpID, EmpName, EmpSalary)?

Answer 1

Use ROW_NUMBER(if you want a single) 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