SQL Query to find Nth highest salary [duplicate]

This question already has answers here: 开发者_StackOverflow How to fetch the nth highest salary from a table without using TOP and sub-query? (18 answers) SQL query to find Nth highest salary from a salary table (11 answers) Closed 9 years ago.

How to find Nth Highest Salary without using any subquery in MS SQL?

---

;WITH cte1
AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS RN, * FROM Salaries
)
SELECT * 
FROM cte1
WHERE RN = 5 <-- Nth highest

---

Check out the row_number function. :)

http://msdn.microsoft.com/en-us/library/ms186734.aspx