How to Retrieve The Last Record in Each Group – MySQL

In this post, we will learn about Retrieve the last record in each group in MySQL which we can perform using the group by clause and max function The basic idea is to group the records by a certain attribute and then find the maximum values which could be from the primary key( where we have assumed that the primary key will be automatically updated using auto increments.

Once you have the maximum primary key value for each group, you can join it back to the original table to get the corresponding record. And to get it more clear with an example pseudo code follow the post:


Retrieve The Last Record

As we have to get the last record of a particular group so we have to make the groups for certain columns and to get the last record we need to use the max function in certain ways which could help in getting or getting the record for bottom for the same we have given an example below have a look here:

FROM orders o
    SELECT customer_ id, MAX( order_ id) AS max_ order_ id
    FROM orders
    GROUP BY customer_ id
) AS m ON o. customer_ id = m. customer_ id AND o. order_ id = m. max_ order_ id;

In this example, we first create a subquery that groups the orders by Costumer id and finds the maximum order id for each group. This subquery is aliased as m. We then join the order table back to the subquery on both the customer id and order id Columns to get the corresponding record for each maximum Order Id. The code given above will result into the records from bottom

Note that this query assumes that the order id in the column is an auto-incrementing integer that represents the order in which the orders were placed. If your table doesn’t have an auto-incrementing primary key, you will need to modify the query to find the maximum value of a different attribute that can be used to order the records within each group.


To learn more about Retrieving the record from bottom in every group in MySQL visit: by stack overflow.

To earn more about MySQL tutorial, Solutions to the problems which might be faced during working on MySQL, and Different concepts related to it to work with it visit: How to Select Only Rows With Max Value On A Column in SQL.


Leave a Comment

%d bloggers like this: