Karan Gandhi Karan Gandhi - 7 months ago 24
SQL Question

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

How to find

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

Answer

Use ROW_NUMBER(if you want a single) or DENSE_RANK(for all related rows):

WITH CTE AS
(
    SELECT EmpID,EmpName,EmpSalar,
           RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
    FROM dbo.Salary
)
SELECT EmpID,EmpName,EmpSalar
FROM CTE
WHERE RN = @NthRow