Karan Gandhi Karan Gandhi - 2 years ago 106
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download