This article talks about how to get the last record in each group in a MySQL table, particularly
- Retrieving the last record in each group using GROUP BY
- Retrieving the last record in each group using JOINS
Let’s get started by creating a table followed by inserting data into it to be used in the example.
# creating the table CREATE TABLE marketing_sales ( id INT auto_increment, sale_person_name VARCHAR(255), no_products_sold INT, primary key (id) ); # inserting data into table INSERT INTO marketing_sales (sale_person_name,no_products_sold) VALUES("Henry",2000); INSERT INTO marketing_sales (sale_person_name,no_products_sold) VALUES("Richa",3000); INSERT INTO marketing_sales (sale_person_name,no_products_sold) VALUES("Henry",40); INSERT INTO marketing_sales (sale_person_name,no_products_sold) VALUES("George",600); INSERT INTO marketing_sales (sale_person_name,no_products_sold) VALUES("Richa",500); INSERT INTO marketing_sales (sale_person_name,no_products_sold) VALUES("Ved",1000); INSERT INTO marketing_sales (sale_person_name,no_products_sold) VALUES("Richa",50); INSERT INTO marketing_sales (sale_person_name,no_products_sold) VALUES("Henry",5000); INSERT INTO marketing_sales (sale_person_name,no_products_sold) VALUES("Ved",100); INSERT INTO marketing_sales (sale_person_name,no_products_sold) VALUES("George",1200); INSERT INTO marketing_sales (sale_person_name,no_products_sold) VALUES("Ved",150); INSERT INTO marketing_sales (sale_person_name,no_products_sold) VALUES("George",200);
SELECT * FROM marketing_sales;
Output:-

View similar article - SELECT ROW WITH MAXIMUM DATE PER USER.
Retrieving the last record in each group using GROUP BY
There are two solutions explained here using the GROUP BY clause. In both these solutions, we will be using the MAX() function to get the maximum value of id and then retrieving the other columns corresponding to this maximum id.
SOLUTION 1:-
Frequently Asked:
SELECT * FROM marketing_sales WHERE id IN (SELECT MAX(id) FROM marketing_sales GROUP BY sale_person_name);
Output:-

Explanation:-
We can see the figure 1.2 that we got the last record inserted for each group-sale_person_name. Here we are selecting the MAX(id) from marketing_sales table GROUP BY sale_person_name in a sub-query and then selecting all the columns from marketing sales table WHERE ids are IN the sub-query.
SOLUTION 2:-
Observe the below query for another solution.
SELECT m1.* FROM marketing_sales m1, (SELECT MAX(id) AS max_id, sale_person_name FROM marketing_sales GROUP BY sale_person_name) marketing_sales_temp WHERE m1.id = marketing_sales_temp.max_id AND m1.sale_person_name = marketing_sales_temp.sale_person_name ORDER BY m1.id;
Output:-

Explanation:-
- Selecting the MAX(id) and sale_person_name from marketing_sales table GROUP BY sale_person_name in a sub-query with alias marketing_sales_temp.
- Then select all the columns from marketing_sales table matching the id and sale_person_name for both marketing_sales table and marketing_sales_temp.
Retrieving the last record in each group using JOINS
Let us now get the last record in each group using JOINS. Observe the below query.
SELECT m1.* FROM marketing_sales m1 LEFT JOIN marketing_sales m2 ON (m1.sale_person_name = m2.sale_person_name AND m1.id < m2.id) WHERE m2.id IS NULL;
Output:-

Explanation:-
- In this query, we are doing LEFT JOIN of marketing_sales table with itself.
- m1 is an alias for the table on the left and m2 alias for the table on the right side.
- JOIN is done ON sale_person_name and comparing that m1.id should be less than m2.id.
- Finally, select all the rows from the join WHERE m2.id is null.
We hope this article helped you to retrieve last retrieving the last record for each group. Good Luck !!!.
This solved an issue I was struggling with for hours. Thank you.
It’s just save my day.
Thanks. Great Article.
Terimaksih atas sharingnya, luar biasa membantu sekali