How to Find Rows with Maximum Column Values, Partitioned by Another Column in MySQL?

MySQL finds the line with the largest column value by the other column in another column

In the database, it is a common analysis task to find the maximum value according to another group of packets. In MySQL, this can be achieved by combining aggregation and filtering.

This article discusses the problem of retrieved lines from the table, which contains the maximum value of a specific column in each group defined in another column. For example, in the "Topten" table provided, the task is to extract for each unique "Home" value, and the maximum value of the corresponding "Datetime" column.

The initial inquiries are as follows, but there are defects:

The query failed to correctly identify the biggest "DateTime" value of each "Home". In order to solve the problem correctly, you need to group "Home" and "Datetime" at the same time in the sub -query:

<code class="language-sql">SELECT
  s1.id,
  s1.home,
  s1.datetime,
  s1.player,
  s1.resource
FROM TopTen s1
JOIN (SELECT
  id,
  MAX(`datetime`) AS dt
FROM TopTen
GROUP BY id) AS s2
  ON s1.id = s2.id
ORDER BY `datetime`</code>

This final inquiry successfully identifies each line of different "Home" values. These lines contain the maximum value of its "Datetime" column, so as to get the expected result:

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

The above is the detailed content of How to Find Rows with Maximum Column Values, Partitioned by Another Column in MySQL?. For more information, please follow other related articles on the PHP Chinese website!