Analytical Query Patterns

Top N Per Group

Rank Then Filter

ROW_NUMBER can rank rows inside each group. A wrapper query can then keep only the top ranked rows.

Program

Play the script to choose how many products per region appear in the report.

top_n_per_group.sql 
CREATE TABLE product_sales (region TEXT, product TEXT, amount INTEGER);
INSERT INTO product_sales VALUES ('East', 'pen', 90), ('East', 'bag', 150), ('East', 'book', 120), ('West', 'pen', 110), ('West', 'bag', 80), ('West', 'book', 140);
WITH params(max_rank) AS (VALUES ()), ranked AS (SELECT region, product, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS sales_rank FROM product_sales) SELECT region, product, amount, sales_rank FROM ranked WHERE sales_rank <= (SELECT max_rank FROM params) ORDER BY region, sales_rank;

CREATE TABLE product_sales (region TEXT, product TEXT, amount INTEGER);
INSERT INTO product_sales VALUES ('East', 'pen', 90), ('East', 'bag', 150), ('East', 'book', 120), ('West', 'pen', 110), ('West', 'bag', 80), ('West', 'book', 140);
WITH params(max_rank) AS (VALUES ()), ranked AS (SELECT region, product, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS sales_rank FROM product_sales) SELECT region, product, amount, sales_rank FROM ranked WHERE sales_rank <= (SELECT max_rank FROM params) ORDER BY region, sales_rank;

CREATE TABLE product_sales (region TEXT, product TEXT, amount INTEGER);
INSERT INTO product_sales VALUES ('East', 'pen', 90), ('East', 'bag', 150), ('East', 'book', 120), ('West', 'pen', 110), ('West', 'bag', 80), ('West', 'book', 140);
WITH params(max_rank) AS (VALUES ()), ranked AS (SELECT region, product, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS sales_rank FROM product_sales) SELECT region, product, amount, sales_rank FROM ranked WHERE sales_rank <= (SELECT max_rank FROM params) ORDER BY region, sales_rank;
ROW_NUMBER `ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC)` ranks products within each region.
top N `sales_rank <= max_rank` keeps the requested number of rows per group.
partition `PARTITION BY region` restarts the ranking for each region.