MySQL 8: Find the min/max value in each group

Introduction

MySQL is a powerful open-source relational database management system often used for web database applications. As data analysis becomes increasingly important for decision-making, being able to manipulate and aggregate data efficiently is crucial. A common requirement is to find the minimum (MIN) or maximum (MAX) values within each group of data. In this tutorial, we will explore several methods of retrieving min/max values in grouped dataset in MySQL 8, starting from basic groupings to more advanced use cases.

Before we begin, ensure that you have MySQL 8 or higher installed, as some features addressed might not be available in earlier versions. Also, you should have a basic understanding of SQL, including SELECT statements, GROUP BY clauses, and aggregate functions.

Using GROUP BY to Find Min/Max Values

At its heart, the GROUP BY statement in SQL is used to arrange identical data into groups. The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

 SELECT column_name(s),
        MIN(column_name) as MinValue,
        MAX(column_name) as MaxValue
 FROM table_name
 GROUP BY column_name(s);
 

Let’s say we have a sales table called sales_data, and we want to find the minimum and maximum sale amounts made by each salesperson.

 SELECT salesperson_id,
        MIN(sale_amount) AS MinSale,
        MAX(sale_amount) AS MaxSale
 FROM sales_data
 GROUP BY salesperson_id;
 

This query returns the minimum and maximum sale amounts for each salesperson.

Window Functions for Min/Max Values

MySQL 8 introduced window functions that can compute aggregate values without collapsing the rows into a single output row per group. This is particularly useful when you want to display the entire row of data along with min or max value for reference.

 SELECT salesperson_id,
        sale_amount,
        MIN(sale_amount) OVER (PARTITION BY salesperson_id) AS MinSale,
        MAX(sale_amount) OVER (PARTITION BY salesperson_id) AS MaxSale
 FROM sales_data;
 

With the above query, each row will include information regarding the specific sale as well as the min/max sale amounts for that salesperson.

Advanced: Using Subqueries to Compute Min/Max

In more complex scenarios, you may need to select additional information that isn’t directly involved in the grouping or aggregates. Subqueries can help achieve this.

 SELECT s.salesperson_id,
        s.sale_amount,
        s.sale_date,
        m.MinSale,
        m.MaxSale
 FROM sales_data s
 JOIN (
     SELECT salesperson_id,
            MIN(sale_amount) as MinSale,
            MAX(sale_amount) as MaxSale
     FROM sales_data
     GROUP BY salesperson_id
 ) m ON s.salesperson_id = m.salesperson_id;
 

In this example, we’ve introduced a subquery that computes the min and max sales for each salesperson and then joined this result back to the original sales table to get the detailed records.

Exploring Group-wise Sorting Limitations

Sometimes, rather than grabbing the min/max aggregate, you want the entire row that holds this value. This can be a bit more complex and typically involves a subquery or a join.

 SELECT a.*
 FROM sales_data a
 INNER JOIN (
     SELECT salesperson_id,
            MIN(sale_amount) AS MinSale
     FROM sales_data
     GROUP BY salesperson_id
 ) as b ON a.salesperson_id = b.salesperson_id AND a.sale_amount = b.MinSale;
 

This query gets the rows from sales_data with the minimum sale for each salesperson. The inner subquery finds the minimum sale per salesperson, and then it is joined against the original table to get the full row details of only those min sale records.

Conclusion

Finding the minimum or maximum value in each group using MySQL 8 provides various avenues to display and use your data effectively. Whether using basic groupings, window functions for added row details or intricate subqueries and joins for complex scenarios, MySQL 8’s functionality caters to a broad array of data analysis objectives.