How Can I Efficiently Find the Maximum Value in a Column for Each Group in a Large Table?

Find the maximum value in the grouped column of a large table

In this example, you need to extract the row with the maximum value of a specific column from each grouping of a large table without performing an inefficient multi-table search. Consider the following form:

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

Your goal is to retrieve the latest round for each unique ID and the corresponding score. The desired output is as follows:

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

Efficient solution using window functions

An efficient method is to use window functions in conjunction with the DISTINCT keyword:

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 uses the following key concepts:

• PARTITION BY: Groups rows by id column.
• MAX(round): Calculate the maximum number of rounds in each partition.
• FIRST_VALUE(score): Retrieve the first (latest) score for each id with the maximum number of rounds.
• DISTINCT: Ensures only unique rows are returned, eliminating duplicate results.

This optimized query efficiently returns the latest round and score for each unique ID without the need to scan the table multiple times, remaining efficient even for large tables.

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