SQL SERVER – Query to Retrieve the Nth Maximum Value

Replace Employee with your table name, and Salary with your column name. Where N is the level of Salary to be determined.

Method :1

SELECT *

FROM Employee E1

WHERE (N-1) = (

SELECT COUNT(DISTINCT(E2.Salary))

FROM Employee E2

WHERE E2.Salary > E1.Salary)

In the above example, the inner query uses a value of the outer query in its filter condition meaning; the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Method:2

SELECT TOP 1 salary

FROM (

SELECT DISTINCT TOP 3 salary

FROM employee

ORDER BY salary DESC) a

ORDER BY salary

Method : 3

select a.Salary from(

select top 3 ROW_NUMBER() OVER(ORDER BY Salary DESC) AS 'ROW_NUMBER',Salary from Employee order by Salary desc

)a where a.ROW_NUMBER=3