How to find third or nᵗʰ maximum salary from salary table?

sql, sql-server, sql-server-2008

Solution

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

Problem

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

Original source

Related problems