How To Get Last Record In Each Group In MySQL

In some cases it may be necessary to select the most recent record or get the most recent record for a particular date, user, ID or any other group. The following SQL query will retrieve the last record in each group in MySQL because there is no built-in function to do this in MySQL. It can also be used to select the last row for each group in PostgreSQL, SQL Server, and Oracle databases.

The following steps will show you how to get the last record in each group in MySQL.

Example:

In our example, let’s say that you have a table orders (name, order_date, amount) that contains sales data for a number of products at the same time.

1.Create Table

#start
create table orders(name varchar(255),order_date date, amount int);
#end

2.Insert Data

#start
insert into orders(name,order_date, amount)
     values('Aa','2023-05-01',2500),
     ('Bb','2023-05-01',3500),
     ('Cb','2023-05-01',12500),
     ('Aa','2023-05-02',4500),
     ('Bb','2023-05-02',6500),
     ('Cc','2023-05-02',10500),
     ('Aa','2023-05-03',1500),
     ('Bb','2023-05-03',2500),
     ('Cc','2023-05-03',18500);
#end

3. Select Table

#start
   select * from orders;
#end

For example, let’s say that you want to get the last record for each group, that is, for each product. In the first step, we are going to use GROUP BY to get the most recent date for each group.

#start
   select name,max(order_date) from orders group by name;
#end

As we now know the most recent date for each group of records, we can join this data with our original table so that we can get the most recent record for each group of records.

#start
      select orders.* from orders,
           (select name,max(order_date) as order_date
                from orders
                group by name) max_sales
             where orders.name=max_sales.name
             and orders.order_date=max_sales.order_date;
#end