Select Top 10 Records for Each Category in MySQL

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. 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 |
 +------+---------+------------+--------+

Bonus Read : Common Table Expression in MySQL

We will select top 10 records by amount column for each category (product). First, we will rank each row within its group (product).

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.

Bonus Read : How to Calculate Margin in MySQL

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 |
 +------+---------+--------+

Bonus Read : How to Select Top Selling Products using SQL

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.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

mm

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!