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

SQL: Identify the record with maximum value in each grouping

Suppose there is a table containing three fields (name, highest value and total) and data, and the task is to retrieve the record with the highest total value for each different name.

Solution:

The following query returns the desired results:

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

Alternative:

Another approach is to use a subquery to find the maximum total value for each name and then join the result with the original table to retrieve the corresponding records:

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

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