Selecting the Last Row in Each GROUP BY Group with MySQL

Method 1: Subquery with MAX() function:

One approach is to use a subquery with the MAX() function to determine the maximum value of the ordering column within each group. We can then join this subquery with the original table to retrieve the corresponding row.

SELECT t1.*
FROM your_table t1
INNER JOIN (
SELECT grouping_column, MAX(ordering_column) AS max_order
FROM your_table
GROUP BY grouping_column
) t2 ON t1.grouping_column = t2.grouping_column AND t1.ordering_column = t2.max_order;

Replace `your_table` with the actual table name, `grouping_column` with the column used for grouping, and `ordering_column` with the column used for determining the order within each group.

Method 2: Using a Derived Table:

Another approach is to use a derived table to obtain the maximum ordering column value for each group. We can then join this derived table with the original table to fetch the corresponding row.

SELECT t1.*
FROM your_table t1
INNER JOIN (
SELECT grouping_column, MAX(ordering_column) AS max_order
FROM your_table
GROUP BY grouping_column
) t2 ON t1.grouping_column = t2.grouping_column AND t1.ordering_column = t2.max_order;

Similar to the previous method, replace `your_table`, `grouping_column`, and `ordering_column` with the appropriate values in your scenario.

Method 3: Using ROW_NUMBER() Window Function (MySQL 8.0+):

If you are using MySQL 8.0 or later, you can leverage the ROW_NUMBER() window function to assign a sequential number to each row within a group based on the order. Then, you can filter the result to retrieve only the rows with a row number of 1.

SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY grouping_column ORDER BY ordering_column DESC) AS row_num
FROM your_table
) t
WHERE row_num = 1;

Ensure that you replace `your_table`, `grouping_column`, and `ordering_column` with the appropriate values in your context.

Retrieving the last row in each GROUP BY group with MySQL can be accomplished using various techniques. In this article, we explored three approaches: using a subquery with the MAX() function, utilizing a derived table, and leveraging the ROW_NUMBER() window function (available in MySQL 8.0+). Depending on your MySQL version and specific requirements, you can choose the method that suits your needs. Feel free to experiment with the provided code examples and adapt them to your own database schema and business logic.

Published on May 19, 2023

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | groupby