How to sort and limit an SQL table by sum of a numerical column grouped by multiple categories?

I have the following SQL table:

I want to see the two most profitable (per sum of price) "ProductID" per "Country" (sorted by Sum per "Country") like this:

How can I perform this?

>Solution :

We can use RANK here in conjunction with an aggregation by country and product:

WITH cte AS (
    SELECT Country, ProductID, SUM(Price) AS Sum,
           RANK() OVER (PARTITION BY Country ORDER BY SUM(Price) DESC) rnk
    FROM yourTable
    GROUP BY Country, ProductID
)

SELECT Country, ProductID, Sum
FROM cte
WHERE rnk <= 2
ORDER BY Country, rnk;