How to Get First Record in Each Group in MySQL

Last updated on July 29th, 2020 at 03:51 am

Sometimes you may need to get first row in each group or min row per group. Here’s how to get first record in each group in MySQL. You can also use this SQL query to get top 1 row in each group in MySQL, PostgreSQL, SQL Server & Oracle. You can use it to select top 1 row for each group.

Table of Contents

How to Get First Record in Each Group in MySQL

Here are the steps to get first record in each group in MySQL.

Let’s say you have a table product_sales(product, order_date,sale) that contains sales data for multiple products.

mysql> create table product_sales(product varchar(255),order_date date, sale int);

mysql> insert into product_sales(product,order_date, sale)
     values('A','2020-05-01',250),
     ('B','2020-05-01',350),
     ('C','2020-05-01',1250),
     ('A','2020-05-02',450),
     ('B','2020-05-02',650),
     ('C','2020-05-02',1050),
     ('A','2020-05-03',150),
     ('B','2020-05-03',250),
     ('C','2020-05-03',1850);

mysql> select * from product_sales;
+---------+------------+------+
| product | order_date | sale |
+---------+------------+------+
| A       | 2020-05-01 |  250 |
| B       | 2020-05-01 |  350 |
| C       | 2020-05-01 | 1250 |
| A       | 2020-05-02 |  450 |
| B       | 2020-05-02 |  650 |
| C       | 2020-05-02 | 1050 |
| A       | 2020-05-03 |  150 |
| B       | 2020-05-03 |  250 |
| C       | 2020-05-03 | 1850 |
+---------+------------+------+

Bonus Read : How to Get Record with Max Value in MySQL

Let’s say you want to get first record in each group, that is, for each product. First we use GROUP BY to get first date for each group.

mysql> select product,min(order_date) from product_sales group by product;
+---------+-----------------+
| product | max(order_date) |
+---------+-----------------+
| A       | 2020-05-01      |
| B       | 2020-05-01      |
| C       | 2020-05-01      |
+---------+-----------------+

Now that we know the first date for each group, we join this result with our original table to get first record by date group.

mysql> select product_sales.* from product_sales,
           (select product,min(order_date) as order_date
                from product_sales
                group by product) max_sales
             where product_sales.product=max_sales.product
             and product_sales.order_date=max_sales.order_date;
+---------+------------+------+
| product | order_date | sale |
+---------+------------+------+
| A       | 2020-05-03 |  250 |
| B       | 2020-05-03 |  350 |
| C       | 2020-05-03 | 1250 |
+---------+------------+------+

If you want to get last or most recent record for each group, use MAX instead of MIN function above.

Bonus Read : How to Get Last Record in Each Group

How to Select Most Recent Record for Each User

Similarly, you can select first record for each user or get oldest record for each id. Let’s say you have the following table user_data(user_id,transaction_date,sale) with user transaction data

mysql> create table user_data(user_id int, transaction_date date, sale int);

mysql> insert into user_data(user_id,transaction_date, sale)
          values('1','2020-05-01',25),
          ('2','2020-05-01',35),
          ('3','2020-05-01',125),
          ('1','2020-05-02',40),
          ('2','2020-05-02',50),
          ('3','2020-05-02',50),
          ('1','2020-05-03',15),
          ('2','2020-05-03',25),
          ('3','2020-05-03',50);


mysql> select * from user_data;
+---------+------------------+------+
| user_id | transaction_date | sale |
+---------+------------------+------+
|       1 | 2020-05-01       |   25 |
|       2 | 2020-05-01       |   35 |
|       3 | 2020-05-01       |  125 |
|       1 | 2020-05-02       |   40 |
|       2 | 2020-05-02       |   50 |
|       3 | 2020-05-02       |   50 |
|       1 | 2020-05-03       |   15 |
|       2 | 2020-05-03       |   25 |
|       3 | 2020-05-03       |   50 |
+---------+------------------+------+

Bonus Read : How to Get Last 15 Days Record in MySQL

First, we get the first date for each user id using GROUP BY.

mysql> select user_id,min(transaction_date) from user_data group by user_id;
+---------+-----------------------+
| user_id | min(transaction_date) |
+---------+-----------------------+
|       1 | 2020-05-01            |
|       2 | 2020-05-01            |
|       3 | 2020-05-01            |
+---------+-----------------------+

Now that we know the oldest date for each user id, we join this result with our original table to get the first record by user group.

mysql> select user_data.* from user_data,
                (select user_id,min(transaction_date) as transaction_date
                     from user_data
                     group by user_id) max_user
                  where user_data.user_id=max_user.user_id
                  and user_data.transaction_date=max_user.transaction_date;
+---------+------------------+------+
| user_id | transaction_date | sale |
+---------+------------------+------+
|       1 | 2020-05-01       |   25 |
|       2 | 2020-05-01       |   35 |
|       3 | 2020-05-01       |  125 |
+---------+------------------+------+

Hopefully, you can get first record in each group in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

MySQL CAST - How to Type Cast in MySQL

How to Compare Null Values in MySQL

MySQL Compare Databases