How to Find the Most Recent Date for Each Model Group in MySQL?

Retrieve latest date by model group in MySQL

In database management systems, extracting the latest date associated with each model group is a common task. Let's explore how to implement this in MySQL.

Suppose the following data is stored in a table named "doc":

<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>

Our goal is to get a result set showing the latest date for each model:

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

For this we can use the GROUP BY clause and the MAX() aggregate function:

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

This query determines the maximum date for each model in the "doc" table. MAX()The function aggregates the date values ​​in each model group and returns the highest value.

If you intend to retrieve all models with overall maximum date, you can use the following query:

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

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