How to Retrieve the Latest Date for Each Group in MySQL?

Get the latest date from MySQL grouped data

Retrieving the latest date associated with each group is useful when working with data grouped by specific categories. In the given scenario, you have a table containing data like this:

<code>| NO | model | date     | 
+---+-------+----------+
| 1  | bee   | 2011-12-01 |
| 2  | bee   | 2011-12-05 |
| 3  | bee   | 2011-12-12 |
| 4  | tar   | 2011-12-13 | </code>

To retrieve the latest date for each model group, use the following query:

<code class="language-sql">SELECT model, MAX(date) AS latest_date
FROM doc
GROUP BY model;</code>

This query will return the following result set:

<code>| model | latest_date | 
+-------+-------------+
| bee   | 2011-12-12  |
| tar   | 2011-12-13  | </code>

Alternatively, if you want to get all models with the largest date, you can use this query:

<code class="language-sql">SELECT model, date AS latest_date
FROM doc
WHERE date IN (SELECT MAX(date) FROM doc);</code>

This will return a result set like this:

<code>| model | latest_date | 
+-------+-------------+
| bee   | 2011-12-12  |
| tar   | 2011-12-13  | </code>

Note that the second method, in case there are multiple models with the same max date, will return all of them. The first method only returns the latest date for each group. Which method you choose depends on your specific needs.

The above is the detailed content of How to Retrieve the Latest Date for Each Group in MySQL?. For more information, please follow other related articles on the PHP Chinese website!