How to Efficiently Retrieve the Highest Value of a Column Grouped by ID in SQL?

SQL Query to Find Maximum Column Value Grouped by ID

This article tackles the problem of efficiently retrieving the highest value from a column, grouped by a unique ID within a database table. Let's illustrate with the "SCORES" table:

<code>ID    ROUND    SCORE
1     1        3
1     2        6
1     3        2
2     1        10
2     2        12
3     1        6</code>

Our goal is to obtain the row containing the maximum "ROUND" value for each "ID", along with its corresponding "SCORE". The desired output is:

<code>ID   ROUND   SCORE
1    3       2
2    2       12
3    1       6</code>

While a subquery approach is possible, it can be inefficient for large datasets. A superior method utilizes window functions:

SELECT DISTINCT
       id
      ,max(round) OVER (PARTITION BY id) AS round
      ,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score
FROM   SCORES
WHERE  id IN (1,2,3)
ORDER  BY id;

This query leverages max(round) OVER (PARTITION BY id) to determine the maximum "ROUND" for each "ID". first_value(score) OVER (PARTITION BY id ORDER BY round DESC) then retrieves the "SCORE" associated with that maximum "ROUND". DISTINCT ensures only one row per "ID" is returned.

This window function approach avoids multiple table scans, providing a significantly more efficient solution for large databases.

The above is the detailed content of How to Efficiently Retrieve the Highest Value of a Column Grouped by ID in SQL?. For more information, please follow other related articles on the PHP Chinese website!