mysql - group by email_id then get oldest date - Stack Overflow

admin管理员组

文章数量:1279055

I am trying to get the oldest date while using a GROUP BY.

Using MySQL 5.7, I have a table called submissions. I can write a query the returns data like this:

SELECT `email_id`,
       `message_id`,
       `submit_dt` 
FROM `submissions` 
WHERE `email_id` = '10';

The above query returns 3 records that look like this:

| email_id | message_id |      submit_dt      | 
-----------------------------------------------
|   10     |   1234     | 2025-01-23 13:30:01 |
-----------------------------------------------
|   10     |   1234     | 2025-01-23 14:00:01 |
-----------------------------------------------
|   10     |   2345     | 2025-02-08 13:00:02 |
-----------------------------------------------

So now I want to return the oldest date of each message_id. Here is the query I wrote:

SELECT `email_id`,
       `message_id`, 
        MIN(`submit_dt`) 
FROM `john_submissions` 
WHERE `email_id` = '10' 
GROUP BY `email_id`,`message_id`,`submit_dt`

But the results are exactly the same as above.

I was hoping to get results that looked like this:

| email_id | message_id |      submit_dt      | 
-----------------------------------------------
|   10     |   1234     | 2025-01-23 13:30:01 |
-----------------------------------------------
|   10     |   2345     | 2025-02-08 13:00:02 |
-----------------------------------------------

How can I re-write my GROUP BY query so that I get the above results? Or is there another way to do this?

I am trying to get the oldest date while using a GROUP BY.

Using MySQL 5.7, I have a table called submissions. I can write a query the returns data like this:

SELECT `email_id`,
       `message_id`,
       `submit_dt` 
FROM `submissions` 
WHERE `email_id` = '10';

The above query returns 3 records that look like this:

| email_id | message_id |      submit_dt      | 
-----------------------------------------------
|   10     |   1234     | 2025-01-23 13:30:01 |
-----------------------------------------------
|   10     |   1234     | 2025-01-23 14:00:01 |
-----------------------------------------------
|   10     |   2345     | 2025-02-08 13:00:02 |
-----------------------------------------------

So now I want to return the oldest date of each message_id. Here is the query I wrote:

SELECT `email_id`,
       `message_id`, 
        MIN(`submit_dt`) 
FROM `john_submissions` 
WHERE `email_id` = '10' 
GROUP BY `email_id`,`message_id`,`submit_dt`

But the results are exactly the same as above.

I was hoping to get results that looked like this:

| email_id | message_id |      submit_dt      | 
-----------------------------------------------
|   10     |   1234     | 2025-01-23 13:30:01 |
-----------------------------------------------
|   10     |   2345     | 2025-02-08 13:00:02 |
-----------------------------------------------

How can I re-write my GROUP BY query so that I get the above results? Or is there another way to do this?

• mysql
• group-by
• mysql-5.7

Share Improve this question Follow edited Feb 25 at 12:28 Ergest Basha 9,01844 gold badges1010 silver badges3030 bronze badges asked Feb 24 at 21:17 John BeasleyJohn Beasley 3,0891111 gold badges5353 silver badges103103 bronze badges 4

• 1 Don't include the column you're aggregating in the GROUP BY list. That creates a different group for each date. – Barmar Commented Feb 24 at 21:23
• I removed message_id from the GROUP BY, but I received an error that reads "Expression #2 SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.submissions.message_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by". – John Beasley Commented Feb 24 at 21:31
• 1 Yuo should remove submit_dt, not message_id. – Barmar Commented Feb 24 at 21:34
• @Barmar - Ah, I see. That did solve my problem. Thank you. – John Beasley Commented Feb 24 at 21:39

Add a comment  | 

1 Answer 1

Sorted by: Reset to default -2

add in your SQL:

ORDER BY `submit_dt` ASC

本文标签： mysqlgroup by emailid then get oldest dateStack Overflow