Skip to content
Advertisement

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

Advertisement

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;
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement