MySQL Select Top N Rows Per Group

Sometimes you may need to select top N rows from each group in MySQL. Here’s how to select top N rows per group in MySQL. You can use it to select top 10 record for each category or select first record of each group.

 

MySQL Select Top N Rows Per Group

Here are the steps to select top N rows per group. Let’s say you have the following table orders(id, product, amount)

mysql> create table product_orders(id int,product varchar(255),amount int);

mysql> insert into product_orders(id, product, amount)
     values(1,'A',250),(2,'B',150),(3,'C',200),
     (4,'A',250),(5,'B',210),(6,'C',125),
     (7,'A',350),(8,'B',225),(9,'C',150);


mysql> select * from product_orders;
+------+---------+--------+
| id   | product | amount |
+------+---------+--------+
|    1 | A       |    250 |
|    2 | B       |    150 |
|    3 | C       |    200 |
|    4 | A       |    250 |
|    5 | B       |    210 |
|    6 | C       |    125 |
|    7 | A       |    350 |
|    8 | B       |    225 |
|    9 | C       |    150 |
+------+---------+--------+

Bonus Read : How to Get Last Week Data in MySQL

 

How to Select Top N Rows Per Group in MySQL

First, we will rank each row within its group (product column) using 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
       FROM product_orders
       ORDER BY product, amount desc;
+------+---------+--------+--------------+-----------------------------+
| id   | product | amount | product_rank | @current_product := product |
+------+---------+--------+--------------+-----------------------------+
|    7 | A       |    350 |            1 | A                           |
|    1 | A       |    250 |            2 | A                           |
|    4 | A       |    250 |            3 | A                           |
|    8 | B       |    225 |            1 | B                           |
|    5 | B       |    210 |            2 | B                           |
|    2 | B       |    150 |            3 | B                           |
|    3 | C       |    200 |            1 | C                           |
|    9 | C       |    150 |            2 | C                           |
|    6 | C       |    125 |            3 | C                           |
+------+---------+--------+--------------+-----------------------------+

In the above query, we have first sorted each record within its group by amount column in descending order, and then ranked it. If you want to sort it in ascending order of amounts, you can do it by changing the ORDER by clause.

SELECT id, product, amount, 
       @product_rank := IF(@current_product = product, @product_rank + 1, 1) 
       AS product_rank,
       @current_product := product 
  FROM product_orders
  ORDER BY product, amount asc;

Next, we use the above query as a subquery, to select top N rows per group (e.g top 2 rows for each category).

Bonus Read : MySQL Copy Database

 

How to select top 2 rows per group

Here’s the SQL query to select top 2 rows for each group using the above method. We will use the above query as subquery and select rows whose rank is less than or equal to 2.

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 product_orders
         ORDER BY product, amount desc) ranked_rows
       where product_rank<=2;
+------+---------+--------+
| id   | product | amount |
+------+---------+--------+
|    7 | A       |    350 |
|    1 | A       |    250 |
|    8 | B       |    225 |
|    5 | B       |    210 |
|    3 | C       |    200 |
|    9 | C       |    150 |
+------+---------+--------+

Bonus Read : MySQL Insert into Select

 

How to select top 10 rows per group

Similarly, you can select top 10 rows from each group using the following query.

 

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 product_orders
         ORDER BY product, amount desc) ranked_rows
       where product_rank<=10;

Hopefully, now you can easily select top N rows per group in 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 dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!