SQL SERVER – Query to Retrieve the Nth Maximum Value

http://blog.sqlauthority.com/2007/04/27/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.

SELECT *FROM Employee E1WHERE (N-1) = (SELECT COUNT(DISTINCT(E2.Salary))FROM Employee E2WHERE 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.