Mysql select first row – MySQL select first row in each group

by

Mysql select first row: In this article we are going to discuss how to get first record or select first row in each group in MySQL.

Let’s assume we have a table sales_details(sale_person_name,no_products_sold,sales_department) that contains sales data for multiple products.

# 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
 ("Rekha",1200,"Kichten Essentials"),
 ("Rekha",5000,"Apperals"),
 ("Rekha",59,"Medicines"),
 ("Vivek",1300,"Kichten Essentials"),
 ("Vivek",600,"Apperals"),
 ("Vivek",570,"Medicines"),
 ("Dev",300,"Kichten Essentials"),
 ("Dev",450,"Apperals"),
 ("Varun",900,"Medicines"),
 ("varun",500,"Kichten Essentials"),
 ("Git",1000,"Apperals"),
 ("Git",2000,"Medicines");

Output:

select * from sale_details;

 

Mysql select first row_1
Here we will discuss three methods for doing this,

Select First Row in Each Group Using Min() Function

Using min() function we will get minimum value of every group and let us see its usage to get the first row of the each group.

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

In above code you can see that we have used min() function which gave us min value of every column and with help of it we will get first record of each group in table.Here we have used groub_by to get first record of each .Which you can see in our output.

Output:

Mysql select first row_2

Select First Row in Each Group Using Join

Here self join is use to get the first record of each group.We have used left join to join 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:

Mysql select first row_2

Select First Row in Each Group Using Not Exists

Here we will use NOT EXISTS to get first row of each group.If subquery returns any rows it means Exits subquery is True and Not Exits subquery is False.

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:
Mysql select first row_2
Conclusion:
In this article we have discussed how to get first record or select first row in each group using different methods in MySQL.Thank You