PARTITION based on a column and GROUP BY another column-CodePudding

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

CodePudding user response：

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