Short Answer
In MySQL 8, use ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY aggregate_value DESC) to rank grouped results. The clearest production form is a CTE:
WITH product_sales AS (
SELECT
category_id,
product_id,
SUM(amount) AS total_amount
FROM order_items
GROUP BY category_id, product_id
),
ranked_products AS (
SELECT
category_id,
product_id,
total_amount,
ROW_NUMBER() OVER (
PARTITION BY category_id
ORDER BY total_amount DESC, product_id
) AS rn
FROM product_sales
)
SELECT category_id, product_id, total_amount
FROM ranked_products
WHERE rn = 1;This gets total sales for each product in each category, numbers products inside each category by total sales, and returns the top product per category.
Can it be written in one SELECT?
If you only need to display the rank, a grouped MySQL 8 query can put SUM(amount) directly inside the window ORDER BY:
SELECT
category_id,
product_id,
SUM(amount) AS total_amount,
ROW_NUMBER() OVER (
PARTITION BY category_id
ORDER BY SUM(amount) DESC, product_id
) AS rn
FROM order_items
GROUP BY category_id, product_id;Practical advice
Use the CTE form when you need WHERE rn <= 3, want to reuse total_amount, or want the execution order to be obvious to future readers.
Top N per group
WITH customer_product_spend AS (
SELECT
customer_id,
product_id,
SUM(quantity * unit_price) AS spend
FROM order_items
GROUP BY customer_id, product_id
),
ranked AS (
SELECT
customer_id,
product_id,
spend,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY spend DESC, product_id
) AS rn
FROM customer_product_spend
)
SELECT customer_id, product_id, spend, rn
FROM ranked
WHERE rn <= 3
ORDER BY customer_id, rn;The extra product_id sort key makes ties deterministic, so repeated runs return the same row order.
ROW_NUMBER vs RANK vs DENSE_RANK
| Function | Ties | Use case |
|---|---|---|
| ROW_NUMBER() | Always assigns unique numbers | Return exactly one row, such as one top product per category |
| RANK() | Same rank for ties, then skips numbers | Competition ranking, such as 1, 1, 3 |
| DENSE_RANK() | Same rank for ties, no skipped numbers | Report ranking, such as 1, 1, 2 |
Common mistakes
Mistake 1: filtering the window alias in WHERE
-- Wrong: WHERE cannot see the SELECT alias rn
SELECT
category_id,
product_id,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY SUM(amount) DESC) AS rn
FROM order_items
GROUP BY category_id, product_id
WHERE rn = 1;Fix it by wrapping the query in a CTE or subquery, then filter rn outside.
Mistake 2: using a window function instead of GROUP BY
A window function does not collapse rows. SUM(amount) OVER (...) returns a window result for each row. If you need one row per product or category, you still need GROUP BY.
Mistake 3: ignoring ties
If two rows have the same total_amount, add a second sort key such as a primary key or date. Otherwise the row numbered first may not match your business rule.
FAQ
Does MySQL 8 support ROW_NUMBER?
Yes. MySQL 8.0 supports window functions including ROW_NUMBER(), RANK(), DENSE_RANK(), and aggregate window functions with OVER.
How should the search query order by sum( be implemented?
Name the aggregate value as total_amount in a CTE, then use ORDER BY total_amount DESC in the outer window function.
What about MySQL 5.7?
MySQL 5.7 does not have window functions. You can simulate ranking with a self-join or correlated subquery, but for production code MySQL 8 is the cleaner path.