How do I find the nth row from a sql query?

Question

I have a table in which data is something like this:

Name    Salary
Tom      10000
John     20000
Ram      20000
Danny    15000
Sandy    14000
Riddle   15000

I can find 2nd highest salary using cte;

with cte
as
(
    select ROW_NUMBER() over (order by Salary desc) as r,
    * from Employee e
)
select * from cte where r=2

But this gives the result 'Ram' with 20000 salary. What I would like returned is every record for people with the nth-ranking salary. For instance, if I'm looking for n=2, the result would be:

Danny    15000
Riddle   15000

How do I modify the query to achieve this?

Answer 1

Use DENSE_RANK():

;WITH cte AS
(
    SELECT DENSE_RANK() OVER (ORDER BY Salary DESC) AS r, *
    FROM Employee e
)
SELECT *
FROM cte
WHERE r = 2

SQL Fiddle