PARTITION based on a column and GROUP BY another column

Question

Please consider the below script:

declare @tbl  Table
(
    CustomerId          INT,
    CountryID           int,
    Amount              int
);

insert into @tbl values
(1,1,100),
(1,2,200),
(1,3,300),
(1,4,400),
(2,1,800),
(2,1,1000),
(3,1,500),
(2,4,200),
(2,3,900),
(3,1,3000),
(5,1,100),
(5,2,200),
(5,4,5000),
(6,1,1000),
(6,3,900),
(7,2,400),
(8,3,4000),
(2,1,100),
(1,1,100)


Declare @Result Table
(
    CountryID           int,
    CustomerID          int,
    SumAmount           int
);

Declare @CountryID      int;

DECLARE db_cursor CURSOR FOR 
    SELECT distinct CountryID 
    FROM @tbl

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @CountryID  

WHILE @@FETCH_STATUS = 0  
BEGIN  

  insert into @Result
  select top 2 @CountryID, CustomerID, SUM(Amount)
  from @tbl
  where CountryID = @CountryID
  group by CustomerId
  order by 3 desc

  FETCH NEXT FROM db_cursor INTO @CountryID 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 

select * 
from @Result

It returns this result :

CountryID   CustomerID  SumAmount
----------------------------------
1               3         3500
1               2         1900
2               7         400
2               5         200
3               8         4000
3               6         900
4               5         5000
4               1         400

In fact I want to get Top 2 customers that have maximum Amount in each Country.

How can I get that result without CURSOR and single query?

Thanks

Does this answer your query? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Sep 03 '22 at 09:34
Well, obviously, you would need to replace `1` with `2`, @Arian; you want the top **two**. — Thom A, Sep 03 '22 at 09:39

score 3 · Accepted Answer · answered Sep 03 '22 at 09:37

The solution is :

WITH T AS
(
SELECT CountryID, CustomerId, SUM(Amount) AS SumAmount, 
       RANK() OVER(PARTITION BY CountryID ORDER BY SUM(Amount) DESC) AS R
FROM   @tbl
GROUP BY CountryID, CustomerId
)
SELECT *
FROM  T
WHERE R <= 2

But remember that when you want a top n rank , you will not systematically have exactly n rows returning because of ex aequo... You can have more, you can have less, depending of which ranking function you use and how many equal mesure you are ranking...

PARTITION based on a column and GROUP BY another column

1 Answers1