How to Find the Maximum Value for Each Group in SQL?

SQL group maximum value search method

This article discusses how to extract the maximum "Total" value record corresponding to each unique "Name" from a table containing multiple "Name" but different "Total" values.

One solution is to use a subquery:

<code class="language-sql">SELECT
  Name, Top, Total
FROM
  sometable
WHERE
  Total = (SELECT MAX(Total) FROM sometable i WHERE i.Name = sometable.Name);</code>

This subquery finds the maximum "total" value for each "name", and the main query filters out the matching records.

Another way is to use nested queries:

<code class="language-sql">SELECT
  Name, Top, Total
FROM
  sometable
INNER JOIN (
    SELECT MAX(Total) AS Total, Name
    FROM sometable
    GROUP BY Name
  ) AS max ON max.Name = sometable.Name AND max.Total = sometable.Total;</code>

This nested query creates a temporary table containing the maximum "total" value for each "name". The main query then joins the sometable table with this temporary table, where the "name" and "total" values ​​match. The result of the connection is the maximum "total" value record corresponding to each unique "name".

The above is the detailed content of How to Find the Maximum Value for Each Group in SQL?. For more information, please follow other related articles on the PHP Chinese website!