Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 RANK's 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?

share|improve this question
2  
You could put the original query using rank() into a subquery and wrap it with a query that filters the results. –  ConcernedOfTunbridgeWells Mar 26 '12 at 16:29
    
That's a neat idea. I can wrap CTE around it and then, do a WHERE rank_col < 3. right? –  UB01 Mar 26 '12 at 16:31
    
@ConcernedOfTunbridgeWells : I think you need to put your comment as an answer –  a1ex07 Mar 26 '12 at 16:31
    
@UB01 - either that or as a subquery –  ConcernedOfTunbridgeWells Mar 26 '12 at 16:31
add comment

2 Answers 2

up vote 9 down vote accepted

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

share|improve this answer
    
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
add comment
select * from (
SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as RN
FROM Table
) a
where a.RN <= 2
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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