Last updated on January 29th, 2021 at 06:37 am
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 get top n results per group, 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!
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.