How to Retrieve the Maximum 'Total' Value for Each Unique 'Name' in SQL?

SQL Tip: Extract maximum value by group

In database queries, it is often necessary to extract the maximum value for each different grouping in the table. This article will solve the problem of how to select the record with the highest "Total" value for each unique "Name" in a table.

Sample tables and data:

Suppose we have the following sample table:

Name	Top	Total
cat	1	10
dog	2	7
cat	3	20
horse	4	4
cat	5	10
dog	6	9

Query target:

Our goal is to extract the following results:

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

Solution:

This can be achieved using the following SQL query:

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

Explanation:

This query uses a subquery in the WHERE clause. The subquery finds the maximum value of "Total" for each group based on "Name". The outer query then filters the table and selects only the records whose "Total" matches this maximum value.

Alternative:

Alternative to using inner joins:

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

Conclusion:

Both methods are effective in extracting the required records from the table by identifying the maximum value of "Total" for each distinct "Name". These queries can be adapted to various database management systems and provide a general method for extracting maximum values ​​from grouped data.

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