How to select the maximum of a column among a group of rows in SQL (MySQL)

In this tutorial we will demonstrate how to select the row with a maximum value for a given column among multiple rows in a table.

Create a table

 CREATE TABLE `sales` (
  `year` int NOT NULL,
  `product` varchar(50) NOT NULL,
  `quantity_sold` int NOT NULL,
  PRIMARY KEY (`year`,`product`)
)

Insert data into the table

insert into sales(year,product,quantity_sold) 
    values
        (2010,"dell",210),
        (2010,"hp",130),
        (2010,"toshiba",30),
        (2011,"dell",410),
        (2011,"hp",500),
        (2011,"macbook",60),
        (2011,"toshiba",70),
        (2012,"dell",360),
        (2012,"hp",600),
        (2012,"macbook",200),
        (2012,"toshiba",150),
        (2013,"accer",80),
        (2013,"dell",400),
        (2013,"hp",580),
        (2013,"macbook",310),
        (2013,"toshiba",120);

Given the data, we would like to find the product with the maximum sales for each year.

mysql> select * from sales order by year, product, quantity_sold;
+------+---------+---------------+
| year | product | quantity_sold |
+------+---------+---------------+
| 2010 | dell    |           210 |
| 2010 | hp      |           130 |
| 2010 | toshiba |            30 |
| 2011 | dell    |           410 |
| 2011 | hp      |           500 |
| 2011 | macbook |            60 |
| 2011 | toshiba |            70 |
| 2012 | dell    |           360 |
| 2012 | hp      |           600 |
| 2012 | macbook |           200 |
| 2012 | toshiba |           150 |
| 2013 | accer   |            80 |
| 2013 | dell    |           400 |
| 2013 | hp      |           580 |
| 2013 | macbook |           310 |
| 2013 | toshiba |           120 |
+------+---------+---------------+
16 rows in set (0.00 sec)

To achieve to this task, we would applay two sql patterns here:

1. Partition
2. Sub-query
3. where clause

Partition is one of the window functions used to aggregate values over a group of columns without aggregating those rows into a sigle row value. We will use the partition function to find the row with the maximum sales for each product into a new column. This will be the inner query as we will us a subquery. Within the outer query, we will then select only the rows we are interested in. In this case, the row where the quantity_sold equals the maximum_sold per for each year.

Stecp 1: Creating the inner query with the partition function. This gives us the new column with the product with the maximum sales for each year.

mysql> select year,product, quantity_sold , max(quantity_sold )  over(partition by year) as max_sales from sales;
+------+---------+---------------+----------+
| year | product | quantity_sold | max_sales |
+------+---------+---------------+----------+
| 2010 | dell    |           210 |      210 |
| 2010 | hp      |           130 |      210 |
| 2010 | toshiba |            30 |      210 |
| 2011 | dell    |           410 |      500 |
| 2011 | hp      |           500 |      500 |
| 2011 | macbook |            60 |      500 |
| 2011 | toshiba |            70 |      500 |
| 2012 | dell    |           360 |      600 |
| 2012 | hp      |           600 |      600 |
| 2012 | macbook |           200 |      600 |
| 2012 | toshiba |           150 |      600 |
| 2013 | accer   |            80 |      580 |
| 2013 | dell    |           400 |      580 |
| 2013 | hp      |           580 |      580 |
| 2013 | macbook |           310 |      580 |
| 2013 | toshiba |           120 |      580 |
+------+---------+---------------+----------+

Step 2: Select rows with quantity_sold equal to max_sales per year using the where clause.

mysql> select * from  
        (select year,product, quantity_sold , max(quantity_sold )  over(partition by year) as max_sales from sales) as inner_tbl 
        where inner_tbl.quantity_sold = inner_tbl.max_sales;
+------+---------+---------------+-----------+
| year | product | quantity_sold | max_sales |
+------+---------+---------------+-----------+
| 2010 | dell    |           210 |       210 |
| 2011 | hp      |           500 |       500 |
| 2012 | hp      |           600 |       600 |
| 2013 | hp      |           580 |       580 |
+------+---------+---------------+-----------+

This gives us the product with the maximum sales for each year.

Conclusion This tutorial touched on how to get a product with the maximum sales for each year within a sales data. We applied the partotion function sub-query and the where clause to perform this task.

This strategy is not limited to finding the maximum among list of rows, we can apply it on all the aggregate functions such as average, minimum, sum and the like.

References: MySQL Partition : https://dev.mysql.com/doc/refman/8.0/en/partitioning.html MySQL Sub-query: https://dev.mysql.com/doc/refman/8.0/en/subqueries.html