How Can I Efficiently Retrieve Rows with the Maximum Value per Group in a Relational Database?

Efficiently Finding Maximum Values within Groups in Relational Databases

Extracting rows containing the maximum values for specific columns while maintaining group integrity within relational databases can be complex. This article addresses the challenge of retrieving the row with the highest round number for each unique ID, prioritizing query efficiency.

One method uses subqueries:

<code class="language-sql">SELECT * FROM
(SELECT id, round,
CASE WHEN (MAX(round) OVER (PARTITION BY id)) = round THEN score ELSE NULL END score
 FROM
 SCORES
 where id in (1,2,3)
) scorevals
WHERE
scorevals.round is not null;</code>

However, this approach is inefficient due to the post-processing filtering step.

For improved performance, consider using window functions:

<code class="language-sql">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;</code>

This query employs window functions to determine the maximum round for each ID and then retrieves the corresponding score. The DISTINCT keyword ensures a single row per ID.

A potentially faster alternative, using the same window function twice:

<code class="language-sql">SELECT DISTINCT
       id
      ,first_value(round) OVER (PARTITION BY id ORDER BY round DESC) 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;</code>

Both optimized solutions avoid unnecessary filtering, leading to faster query execution times compared to the subquery approach.

The above is the detailed content of How Can I Efficiently Retrieve Rows with the Maximum Value per Group in a Relational Database?. For more information, please follow other related articles on the PHP Chinese website!