Retrieving the last record in each group[Solved] – MySQL

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:-

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:-

1. 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.
2. 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:-

1. In this query, we are doing LEFT JOIN of marketing_sales table with itself.
2. m1 is an alias for the table on the left and m2 alias for the table on the right side.
3. JOIN is done ON sale_person_name and comparing that m1.id should be less than m2.id.
4. 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 !!!.