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

Question

How to find third or n^th maximum salary from salary table(EmpID, EmpName, EmpSalary) in optimized way?

Answer 1

Row Number :

SELECT Salary,EmpName FROM   (    SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum    FROM EMPLOYEE    ) As A WHERE A.RowNum IN (2,3) 

Sub Query :

SELECT * FROM Employee Emp1 WHERE (N-1) = (                SELECT COUNT(DISTINCT(Emp2.Salary))                FROM Employee Emp2                WHERE Emp2.Salary > Emp1.Salary                ) 

Top Keyword :

SELECT TOP 1 salary FROM (       SELECT DISTINCT TOP n salary       FROM employee       ORDER BY salary DESC       ) a ORDER BY salary 

Answer 2

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