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 );
- 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;
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 );
- 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!!