Is there a way to show only the top 2/nth of a query after “group by” country? – bigquery SQL

I am doing a query on Google Big query, I have joined the 2 tables and created a new column “total gmv” using “SUM” to represent the total revenue, now I wanted to show only the top 2 vendors , GROUP BY country in my query.

I manage to show total_gmv group by COUNTRY and vendor_name, but I would like to filter to show top 2 vendors for each country only.

Code I used

SELECT Ord.country_name, vn.vendor_name, round(sum(Ord.gmv_local),2) as total_gmv FROM ORDERS as Ord
left join `primeval-falcon-306603.foodpanda_BI_Exercise.Vendors` as vn
ON Ord.vendor_id = vn.id
GROUP BY Ord.country_name, vn.vendor_name
ORDER BY Ord.country_name, total_gmv desc

country_name	vendor_name	total_gmv
Singapore	A House	1583.25
Singapore	B House	1236.35
Singapore	C House	1153.27
Singapore	D House	596.21
Hong Kong	H House	1888.75
Hong Kong	K House	755.78
Bangkok	BB House	936.12

Is there a way to show only the top 2 vendors per country?

My target table should look like this, showing top 2 only

I am using Google bigquery and it seems the “TOP 2” function doesn’t work?

country_name	vendor_name	total_gmv
Singapore	A House	1583.25
Singapore	B House	1236.35
Hong Kong	H House	1888.75
Hong Kong	K House	755.78
Bangkok	BB House	936.12

Answer

Use ROW_NUMBER:

WITH cte AS (
    SELECT Ord.country_name, vn.vendor_name, ROUND(SUM(Ord.gmv_local), 2) AS total_gmv,
           ROW_NUMBER() OVER (PARTITION BY Ord.country_name
                              ORDER BY SUM(Ord.gmv_local) DESC) rn
    FROM ORDERS AS Ord
    LEFT JOIN `primeval-falcon-306603.foodpanda_BI_Exercise.Vendors` AS vn
        ON Ord.vendor_id = vn.id
    GROUP BY Ord.country_name, vn.vendor_name
)

SELECT country_name, vendor_name, total_gmv
FROM cte
WHERE rn <= 2
ORDER BY country_name, total_gmv DESC;