Limit results to the first 2 ranking rows

Question

In SQL Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from rank 1, 2, 3......999. But I want only up to 2 RANKs in each PARTITION.

Example:

ID   Name    Score    Subject
1    Joe      100      Math
2    Jim      99       Math
3    Tim      98       Math
4    Joe      99       History
5    Jim      100      History
6    Tim      89       History
7    Joe      80       Geography
8    Tim      100      Geography
9    Jim      99       Geography

I want the result to be:

SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC)
FROM Table

Subject        Name      Rank
Math           Joe        1
Math           Jim        2
History        Jim        1
History        Joe        2
Geography      Tim        1
Geography      Jim        2

I want only rank 1 and 2 in each category. How do I do this?

Answer 1

You could put the original query using rank() into a subquery and wrap it with a query that filters the results.

Answer 2

select * from (
SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as RN
FROM Table
) a
where a.RN <= 2

Answer 3

I think the way to do this in SQL Server is to combine the window function with a common table expression:

with cte as (
SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as ordinal
FROM Table
)
select * from cte where ordinal <= 2

Answer 4

For Teradara alternatively you can do:

SELECT 
Subject, 
Name, 
RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as RN
FROM Table
QUALIFY a.RN <= 2