How to Retrieve the Latest Dates for Each Model Group in MySQL?

How to get the latest date of each model group in MySQL database?

This article will introduce how to extract the latest date of each model group from grouped data in a MySQL database.

Get latest date from grouped MySQL data

Assuming the database table contains details of models and their corresponding dates, the goal is to extract the latest date for each model group. This can be achieved using SQL queries.

Let’s break down the original query:

<code class="language-sql">SELECT model, date
FROM doc
WHERE date ........????? //接下来是什么？
GROUP BY model</code>

The missing placeholder is a conditional expression that determines the latest date. One way is to use the max() function:

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

This query calculates the maximum date value for each model, effectively providing the latest date for each group.

Other query methods

• All models including maximum date:

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

This query retrieves all models whose date matches the maximum date in the entire table.

• Detailed records with latest date:

  <code class="language-sql">  SELECT d.model, d.date, d.color, d.etc
    FROM doc d
    WHERE d.date IN (SELECT max(d2.date) FROM doc d2 WHERE d2.model=d.model)</code>

This query retrieves detailed records for each model that match the latest date in their respective group.

• Use OVER clause (MySQL 8.0):

  <code class="language-sql">  SELECT model, date, color, etc
    FROM (SELECT model, date, color, etc,
    max(date) OVER (PARTITION BY model) max_date
    FROM doc) predoc
    WHERE date=max_date;</code>

For large data sets, this query provides a faster solution by using the OVER clause, which calculates the maximum date for each model group and selects only the latest records.

Through the above methods, you can choose the appropriate SQL statement according to your needs to efficiently obtain the latest date of each model group. For MySQL 8.0 and above, it is recommended to use the OVER clause method because it is more efficient when processing large data sets.

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