Replace Employee with your table name, and Salary with your column name. Where N is the level of Salary to be determined. Let us see a query to retrieve the Nth Maximum Value.
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.
In above query where you see (N-1), you can replace the N with any other number based on your need. For example, if you want to find the 10th highest record, you can just write (10-1) or just 9 there and it will bring back necessary record. It is a very easy query and requires one time proper understanding how it works.
Here is another relevant blog post which has working examples of AdventureWorks database: SQL SERVER – Find Nth Highest Record from Database Table
Let me know if you have any question in the comments section. I keep on sharing the various tips, I suggest you sign up for my newsletter http://go.sqlauthority.com
Reference : Pinal Dave (http://blog.SQLAuthority.com)