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_ordersORDER 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 whereproduct_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 whereproduct_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!