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

Get the maximum value of each group in the table

To get the maximum value for each grouping in a table, a common approach is to use a subquery to determine the maximum value for each grouping, and then use that subquery to filter and select relevant records.

Please consider the sample table provided:

<code><h2>名称    数值   其他列</h2><p>泵 1  8000.0  内容1<br></br>泵 1  10000.0 内容2<br></br>泵 1  10000.0 内容3<br></br>泵 2  3043    内容4<br></br>泵 2  4594    内容5<br></br>泵 2  6165    内容6<br></br></p></code>

To retrieve the maximum value for each pump, you can use the following query:

<code>SELECT name, MAX(value)
FROM out_pumptable
GROUP BY name</code>

This query groups the records by the Name column and calculates the maximum value within each grouping, thus providing the highest value for each pump.

The "GROUP BY" clause plays a vital role in this query. By grouping records, it allows an aggregate function ("MAX") to operate on the values ​​within each grouping, effectively identifying the maximum value for each pump.

Using this query you can get the following results:

<code><h2>名称    数值</h2><p>泵 1  10000.0<br></br>泵 2  6165.0<br></br></p></code>

This result provides the maximum value for Pump 1 and Pump 2, solving the problem of multiple entries with the same value in a specific grouping.

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