MySQL select first row in each group

This article will be discussing how to get the first row from each group of a table/query. We will be going through three different ways to achieve the same output.

Table of Contents:-

• SELECT FIRST ROW IN EACH GROUP USING MIN() FUNCTION
• SELECT FIRST ROW IN EACH GROUP USING JOIN
• SELECT FIRST ROW IN EACH GROUP USING NOT EXISTS

Let us get started by making the sample data. We will be creating a table sale_details followed by inserting some rows into it.

# create the table sale_details
CREATE TABLE sale_details (
    id INT auto_increment,
    sale_person_name VARCHAR(255),
    no_products_sold INT,
    sales_department VARCHAR(255),
    primary key (id)
);
#insert rows into sale_details
 INSERT INTO sale_details (sale_person_name,no_products_sold,sales_department) 
  VALUES
 ("Henry",2000,"Kichten Essentials"),
 ("Henry",5000,"Apperals"),
 ("Henry",40,"Medicines"),
 ("Richa",3000,"Kichten Essentials"),
 ("Richa",500,"Apperals"),
 ("Richa",50,"Medicines"),
 ("Ved",100,"Kichten Essentials"),
 ("Ved",150,"Apperals"),
 ("Ved",1000,"Medicines"),
 ("George",600,"Kichten Essentials"),
 ("George",1200,"Apperals"),
 ("George",200,"Medicines");

To view the snapshot of the table sale_details, execute:

SELECT * FROM sale_details;

The output in image_1 shows that the data of the table can be grouped by sale_person_name.

Example: We will be retrieving the first record of each group based on student_person_name

We will be using three different solutions for the above-stated problem. Observe the queries in each section.

SELECT FIRST ROW IN EACH GROUP USING MIN() FUNCTION

MIN() function in MySQL will return the minimum value of a column in a query. Let us see its usage to retrieve the first row of the group.

SELECT *
  FROM sale_details 
 WHERE id IN (
               SELECT min(id) 
                 FROM sale_details 
                GROUP BY sale_person_name
             );

Output:-

Explanation:-

• The output in image_2 shows that the record which occurred first in the table sale_details is retrieved.
• Here we are getting the rows with minimum id value.
• The query above is divided into an inner and an outer query.
• The inner query gets the minimum value from the id column grouped by sale_person_name.
• The outer query then gets all the other column values for the id values returned by the inner query.

SELECT FIRST ROW IN EACH GROUP USING JOIN

In this section, we will be doing a self join to get the desired output. A self join is a join to the table itself.

 SELECT
    sd1.*
FROM
    sale_details AS sd1
    LEFT JOIN sale_details AS sd2 ON (
        sd2.sale_person_name = sd1.sale_person_name
        AND
        sd2.id < sd1.id
    )
WHERE
    sd2.sale_person_name IS NULL;

Output:-

SELECT FIRST ROW IN EACH GROUP USING NOT EXISTS

If the subquery returns any rows, it means NOT EXISTS subquery is FALSE. Similarly, If the subquery returns any rows, it means EXISTS subquery is TRUE. Let us now see how to get the first row of each group using NOT EXISTS.

SELECT * FROM sale_details sd1
WHERE NOT EXISTS (
   SELECT 1 FROM sale_details sd2 
   WHERE sd2.sale_person_name = sd1.sale_person_name 
   AND sd2.ID < sd1.ID 
);

Output:-

READ MORE

• MySQL select first row
• MySQL Select last N rows
• MySQL select rows by range
• MySQL select TOP 10 rows
• MySQL select row with max value

We hope this article helped you get the first record of each group from the table or a query in MySQL. Good Luck!!