SQL Server 2012: Select Top n based on multiple criteria

Question

Pretty new to SQL here - help would be much appreciated. I have a table with Region, Month, Member ID, and Sales (with multiple transactions per member). I just want to extract the top 2 members, based on sum of sales, per region, per month....so essentially:

Region   Month     MemberID   Sales
-----------------------------------------
  1      1/1/2013     A       $200 
  2      2/1/2013     B       $300 
  1      1/1/2013     A       $100 
  1      1/1/2013     B        $50 
  2      1/1/2013     D       $500 
  2      2/1/2013     C       $200 

Becomes:

Region  Month   Member ID   Sales
-----------------------------------------
1   1/1/2013    A    $300 
1   1/1/2013    B    $50 
2   1/1/2013    D    $500 
2   1/1/2013    B    $200 

Ultimately, there will be 10 regions, and I'd like to take the top 5 sales by member for each region, each month.

Answer 1

You can do this with row_number():

select region, month, MemberId, sales
from (select region, month, MemberId, sum(sales) as sales
             row_number() over (partition by region, month order by sum(sales) desc) as seqnum
      from table t
      group by region, month, MemberId
     ) t
where seqnum <= 2;