Karan Gandhi Karan Gandhi - 1 year ago 53
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