Last updated on June 19th, 2024 at 10:11 am
When you run an ecommerce business or online store, you may need to select top 10 records for each category of products, brands, etc. for reporting and analysis. MySQL database makes it very easy to carry out this kind of querying. In fact, many web developers need to be able to fetch top N results in each group of their data. In this article, we will look at how to select top 10 records for each category in MySQL.
Select Top 10 Records for Each Category
Here are the steps to select top 10 records for each category. Let us say you have the following table sales(id, product, order_date, amount).
mysql> create table sales(id int,
product varchar(255),
order_date date,
amount int);
mysql> insert into sales(id, product, order_date, amount)
values(1,'A','2021-01-01',125),
(2,'A','2021-01-02',225),
(3,'A','2021-01-03',325),
(4,'A','2021-01-04',105),
(5,'A','2021-01-05',150),
(6,'A','2021-01-06',215),
(7,'A','2021-01-07',205),
(8,'A','2021-01-08',125),
(9,'A','2021-01-09',350),
(10,'A','2021-01-10',235),
(11,'A','2021-01-11',325),
(12,'B','2021-01-01',150),
(13,'B','2021-01-02',165),
(14,'B','2021-01-03',145),
(15,'B','2021-01-04',115),
(16,'B','2021-01-05',105),
(17,'B','2021-01-06',195),
(18,'B','2021-01-07',185),
(19,'B','2021-01-08',175),
(20,'B','2021-01-09',165),
(21,'B','2021-01-10',115),
(22,'B','2021-01-11',125);
mysql> select * from sales;
+------+---------+------------+--------+
| id | product | order_date | amount |
+------+---------+------------+--------+
| 1 | A | 2021-01-01 | 125 |
| 2 | A | 2021-01-02 | 225 |
| 3 | A | 2021-01-03 | 325 |
| 4 | A | 2021-01-04 | 105 |
| 5 | A | 2021-01-05 | 150 |
| 6 | A | 2021-01-06 | 215 |
| 7 | A | 2021-01-07 | 205 |
| 8 | A | 2021-01-08 | 125 |
| 9 | A | 2021-01-09 | 350 |
| 10 | A | 2021-01-10 | 235 |
| 11 | A | 2021-01-11 | 325 |
| 12 | B | 2021-01-01 | 150 |
| 13 | B | 2021-01-02 | 165 |
| 14 | B | 2021-01-03 | 145 |
| 15 | B | 2021-01-04 | 115 |
| 16 | B | 2021-01-05 | 105 |
| 17 | B | 2021-01-06 | 195 |
| 18 | B | 2021-01-07 | 185 |
| 19 | B | 2021-01-08 | 175 |
| 20 | B | 2021-01-09 | 165 |
| 21 | B | 2021-01-10 | 115 |
| 22 | B | 2021-01-11 | 125 |
+------+---------+------------+--------+
There are several ways to pick top N records from each group in a table. We will look at the most common solutions to this problem.
1. Using User-Defined Variable
We will select top 10 records by amount column for each category (product). First, we will rank each row within its group (product). You can do this with the following SQL query.
mysql> SELECT id, product, amount,
@product_rank := IF(@current_product = product, @product_rank + 1, 1) AS product_rank,
@current_product := product as current_product
FROM product_orders
ORDER BY product, amount desc;
+------+---------+--------+--------------+-----------------+
| id | product | amount | product_rank | current_product |
+------+---------+--------+--------------+-----------------+
| 9 | A | 350 | 1 | A |
| 3 | A | 325 | 2 | A |
| 11 | A | 325 | 3 | A |
| 10 | A | 235 | 4 | A |
| 2 | A | 225 | 5 | A |
| 6 | A | 215 | 6 | A |
| 7 | A | 205 | 7 | A |
| 5 | A | 150 | 8 | A |
| 1 | A | 125 | 9 | A |
| 8 | A | 125 | 10 | A |
| 4 | A | 105 | 11 | A |
| 17 | B | 195 | 1 | B |
| 18 | B | 185 | 2 | B |
| 19 | B | 175 | 3 | B |
| 20 | B | 165 | 4 | B |
| 13 | B | 165 | 5 | B |
| 12 | B | 150 | 6 | B |
| 14 | B | 145 | 7 | B |
| 22 | B | 125 | 8 | B |
| 15 | B | 115 | 9 | B |
| 21 | B | 115 | 10 | B |
| 16 | B | 105 | 11 | B |
+------+---------+--------+--------------+-----------------+
In above query, we use temporary variables product_rank and current_product to keep track of latest rank value and product value.
Next, we will use the above query as a subquery, to select top 10 records for each product.
mysql> select id, product, amount
from ( SELECT id, product, amount,
@product_rank := IF(@current_product = product, @product_rank + 1, 1) AS product_rank,
@current_product := product FROM sales ORDER BY product, amount desc )
ranked_rows
where product_rank<=10;
+------+---------+--------+
| id | product | amount |
+------+---------+--------+
| 9 | A | 350 |
| 3 | A | 325 |
| 11 | A | 325 |
| 10 | A | 235 |
| 2 | A | 225 |
| 6 | A | 215 |
| 7 | A | 205 |
| 5 | A | 150 |
| 1 | A | 125 |
| 8 | A | 125 |
| 17 | B | 195 |
| 18 | B | 185 |
| 19 | B | 175 |
| 20 | B | 165 |
| 13 | B | 165 |
| 12 | B | 150 |
| 14 | B | 145 |
| 22 | B | 125 |
| 15 | B | 115 |
| 21 | B | 115 |
+------+---------+--------+
We have used the condition where product_rank<=10 to select only top 10 records. You can modify it to select as many rows as you want.
2. Using Row_number()
Starting MySQL 8+, you can use Row_number() for this purpose. It is a powerful window function that allows you to assign a sequential number for each row in a partition/group.
Here is the basic SQL query to get top N rows per group in a table. Here we group the table using grouping_column and assign row number for each row within each group and store it in row_num column. We use this result in subquery to select rows where row_num<=N.
SELECT * FROM (
SELECT column_names,
ROW_NUMBER() OVER (PARTITION BY grouping_column ORDER BY ordering_column DESC) AS row_num
FROM table_name
) temp
WHERE row_num <= N;
We use the above query to assign row number for each row for product groups. We store the row numbers in row_num column.
SELECT id, product, amount,
ROW_NUMBER() OVER (PARTITION BY product ORDER BY amount DESC) AS row_num
FROM sales
We use the result of above query as a subquery to get top N rows for each group, that is, product.
SELECT *
FROM (
SELECT id, product, amount,
ROW_NUMBER() OVER (PARTITION BY grouping_column ORDER BY ordering_column DESC) AS row_num
FROM sales
) temp
WHERE row_num <= 10;
+------+---------+--------+
| id | product | amount |
+------+---------+--------+
| 9 | A | 350 |
| 3 | A | 325 |
| 11 | A | 325 |
| 10 | A | 235 |
| 2 | A | 225 |
| 6 | A | 215 |
| 7 | A | 205 |
| 5 | A | 150 |
| 1 | A | 125 |
| 8 | A | 125 |
| 17 | B | 195 |
| 18 | B | 185 |
| 19 | B | 175 |
| 20 | B | 165 |
| 13 | B | 165 |
| 12 | B | 150 |
| 14 | B | 145 |
| 22 | B | 125 |
| 15 | B | 115 |
| 21 | B | 115 |
+------+---------+--------+
3. Using Rank()
MySQL >= 8 also supports rank() and dense_rank() window functions that are similar to row_number() function mentioned above. Both basically let you group rows in table and rank rows within each group. Rank() returns ranking with gaps whereas dense_rank() returns ranking without gaps, that is, in case of ties.
Here is the basic SQL query to rank your table.
SELECT column_names,
DENSE_RANK() OVER (PARTITION BY grouping_column ORDER BY ordering_column DESC) AS rank
FROM
table_name;
We will use this query to group our table by product and rank rows within each group.
SELECT id, product, amount,
DENSE_RANK() OVER (PARTITION BY product ORDER BY amount DESC) AS rank
FROM
sales;
We use the above query as a subquery and select top N rows(N=10) in it.
SELECT * FROM (
SELECT id, product, amount,
DENSE_RANK() OVER (PARTITION BY product ORDER BY amount DESC) AS rank
FROM
sales;
) temp
WHERE row_num <= 10;
+------+---------+--------+
| id | product | amount |
+------+---------+--------+
| 9 | A | 350 |
| 3 | A | 325 |
| 11 | A | 325 |
| 10 | A | 235 |
| 2 | A | 225 |
| 6 | A | 215 |
| 7 | A | 205 |
| 5 | A | 150 |
| 1 | A | 125 |
| 8 | A | 125 |
| 17 | B | 195 |
| 18 | B | 185 |
| 19 | B | 175 |
| 20 | B | 165 |
| 13 | B | 165 |
| 12 | B | 150 |
| 14 | B | 145 |
| 22 | B | 125 |
| 15 | B | 115 |
| 21 | B | 115 |
+------+---------+--------+
When we use dense_rank(), in case of ties, the next rank will not be skipped. In case of rank() function, it will be skipped. In other words, when you use dense_rank() and there is a tie, then all those rows will be assigned the same rank and the next row will have a rank 1 more than previous rank. In case of rank() function, when there is a tie, all those rows will be assigned the same rank and the next row will have rank=previous rank + no. of duplicates. Depending on your requirement, you can use rank() or dense_rank() function.
Conclusion
In this article, we have learnt how to select top 10 records from each group in a table. If you are using MySQL >=8.0 then you can use rank(), dense_rank() or row_number() functions to quickly partition your table, and rank rows within each group. For older versions, you can use user-defined variables as mentioned in solution #1.
This is a commonly required analysis in almost every organization. Typically, teams need to generate regular reports on top N products, categories, brands, territories, and more. You can use any of the above methods to easily generate such reports.
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Also Read
Common Table Expression in MySQL
How to Select Top Selling Products using SQL
How to Calculate Margin in MySQL
MySQL Query to Get Top Selling Products
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.