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

Find the largest record by group in SQL

In database management, it is often necessary to retrieve the largest record in each data group. Consider the following scenario.

Question:

You have a table with three fields: "Name", "Top" and "Total", with the following data:

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

Your task is to find the record with the largest "Total" value for each unique "Name" value. The desired result should be:

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

Solution:

To retrieve the maximum records per group, you can use the following 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>

This query compares the "Total" value of each record with its maximum "Total" value in the same "Name" group. Then select the records whose "Total" value matches.

Alternatively, you can use a subquery to achieve the same result:

<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 first uses a subquery to calculate the maximum "Total" value for each unique "Name". The main query then joins the table with a subquery to select only the records with matching maximum "Total" value.

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