22

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?

15

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

  • Makes sense. I wish Microsoft would make it more simple i.e. put a number in the RANK function. RANK(2) OVER (PARTITION BY Col2 ORDER B Y Col3) AS Top_2_Ranks. May be it will happen in future releases. Thanks for the idea though. – UB01 Mar 26 '12 at 16:38
  • @UB01: Or better yet, it would be nice to use windowed functions in a WHERE clause. – Jon of All Trades May 11 '12 at 21:57
16
select * from (
SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as RN
FROM Table
) a
where a.RN <= 2
0

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
-1

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
  • 3
    Well, that might be all right, but the question is specifically about SQL Server. – dezso Nov 7 '16 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.