How to Find the Maximum Total for Each Name Group in SQL?

SQL group query maximum value

This question is designed to extract specific data from a table, with the goal of finding the record with the highest "Total" value for each unique "Name" field. This is different from the problem of finding the last record of each group.

Here are two SQL query methods to achieve this goal:

Method 1: 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 query uses a subquery to find the maximum Name value in each Total group and then selects only the records that match that maximum value.

Method 2: Self-connection

<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 query uses INNER JOIN to join sometable with a subquery that calculates the maximum Name value for each Total group. The INNER JOIN condition ensures that only records whose Total value matches the maximum value for each group are selected.

Both methods give the same result:

Name	Top	Total
cat	3	20
horse	4	4
dog	6	9

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