The top few in every group
"The top 3 products in each category" is one of the most common questions in analysis. You can't
filter on a window function in WHERE (it's worked out after WHERE runs), so number the rows in
a CTE first, then filter:
WITH ranked AS (
SELECT name, category, list_price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY list_price DESC, id) AS rn
FROM products
)
SELECT category, name, list_price
FROM ranked
WHERE rn <= 3Use ROW_NUMBER for exactly N per group, or RANK if a tie for third should bring in a fourth row.
The same pattern answers "each customer's first order" (PARTITION BY customer_id ORDER BY order_date, keep rn = 1) and "the latest price for each product".
In other databases
SELECT category, name FROM products QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY list_price DESC) <= 3QUALIFY filters on a window function directly. The CTE way works here too.
WITH ranked AS (SELECT category, name, ROW_NUMBER() OVER (PARTITION BY category ORDER BY list_price DESC) AS rn FROM products)
SELECT category, name FROM ranked WHERE rn <= 3No QUALIFY: number the rows in a CTE, then filter.
WITH ranked AS (SELECT category, name, ROW_NUMBER() OVER (PARTITION BY category ORDER BY list_price DESC) AS rn FROM products)
SELECT category, name FROM ranked WHERE rn <= 3Window functions need MySQL 8 or later.
WITH ranked AS (SELECT category, name, ROW_NUMBER() OVER (PARTITION BY category ORDER BY list_price DESC) AS rn FROM products)
SELECT category, name FROM ranked WHERE rn <= 3SELECT category, name FROM products WHERE TRUE QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY list_price DESC) <= 3BigQuery has QUALIFY, but wants a WHERE, GROUP BY or HAVING with it: WHERE TRUE does.