How to Select Rows with the Maximum Datetime for Each Home in MySQL?

In mysql, select the line with the maximum values ​​according to another group

Question statement

Consider the following player performance table:

The goal is to retrieve the rows of each different Home column, and consider the maximum value of each home. In addition, the results should include other columns (Player, etc.).

<code class="language-sql">CREATE TABLE TopTen (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  home INT UNSIGNED NOT NULL,
  `datetime` DATETIME NOT NULL,
  player VARCHAR(6) NOT NULL,
  resource INT NOT NULL
);</code>

Example data

The expected results

<code class="language-sql">INSERT INTO TopTen
  (id, home, `datetime`, player, resource)
VALUES
  (1, 10, '2009-04-03', 'john', 399),
  (2, 11, '2009-04-03', 'juliet', 244),
  (5, 12, '2009-04-03', 'borat', 555),
  (3, 10, '2009-03-03', 'john', 300),
  (4, 11, '2009-03-03', 'juliet', 200),
  (6, 12, '2009-03-03', 'borat', 500),
  (7, 13, '2008-12-24', 'borat', 600),
  (8, 13, '2009-01-01', 'borat', 700)
;</code>

Solution

<code>id  home    datetime       player  resource
1   10     2009-04-03     john    399
2   11     2009-04-03     juliet  244
5   12     2009-04-03     borat   555
8   13     2009-01-01     borat   700</code>

The following query provides the results required:

This query executes the following steps:

It selects all columns from the Topten table (alias TT).

<code class="language-sql">SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime;</code>

It combines TT and Sub -query (alias groupttt) with internal connection, and the sub -query retrieval to retrieve the maximum value of each different home value.

The connection conditions ensure matching the home and doteTime value in the two tables.
The result contains all columns from the Topten table. Press the home group and press the maximum value of each home.
3. Note: I have revised the date format so that it meets the standard
format to ensure that MySQL can be correctly analyzed. This will not change the logic of the query, but only ensure the correctness of the data.

The above is the detailed content of How to Select Rows with the Maximum Datetime for Each Home in MySQL?. For more information, please follow other related articles on the PHP Chinese website!