How to Efficiently Select the Maximum Value for Each Group in a SQL Database?

Extracting Maximum Values for Each Group in SQL

A common SQL task involves retrieving the maximum value for each unique group within a table. Let's illustrate this with an example table:

Name	Value	AnotherColumn
Pump 1	8000.0	Something1
Pump 1	10000.0	Something2
Pump 1	10000.0	Something3
Pump 2	3043	Something4
Pump 2	4594	Something5
Pump 2	6165	Something6

Our goal is to find the highest Value for each Name (pump), yielding this result:

Name	Value
Pump 1	10000.0
Pump 2	6165

A naive approach using subqueries to find the maximum value for each name often leads to duplicate entries when multiple rows share the same maximum value:

<code class="language-sql">select a.name, value 
from out_pumptable as a,
(select name, max(value) as value from out_pumptable group by name) g
where a.name = g.name and g.value = a.value</code>

However, a more efficient and concise solution utilizes the GROUP BY clause:

<code class="language-sql">select name, max(value)
from out_pumptable
group by name;</code>

This query groups rows based on the Name column and then selects the maximum Value within each group, effectively avoiding duplicate results and providing the desired output.

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