How to Efficiently Find Minimum and Maximum Values per Group in SQL Without Using Ranking Functions?

Get Highest and Smallest Values per Group

Overview

This tutorial addresses the question of how to retrieve records with the highest or smallest values for a specified field within each group, without relying on ranking (@Rank). While this initial question employed @Rank as a solution, subsequent responses demonstrate a more efficient approach using joins.

Solution

To obtain the row with the highest OrderField for each group, employ a left outer join and the following query:

SELECT t1.*
FROM `Table` AS t1
LEFT OUTER JOIN `Table` AS t2
  ON t1.GroupId = t2.GroupId AND t1.OrderField < t2.OrderField
WHERE t2.GroupId IS NULL
ORDER BY t1.OrderField;

If multiple records have the same OrderField within a group and you require one of them, you can extend the condition as follows:

SELECT t1.*
FROM `Table` AS t1
LEFT OUTER JOIN `Table` AS t2
  ON t1.GroupId = t2.GroupId 
        AND (t1.OrderField < t2.OrderField 
         OR (t1.OrderField = t2.OrderField AND t1.Id < t2.Id))
WHERE t2.GroupId IS NULL

This refined query ensures that t1 is returned only when no other row t2 exists within the same group GroupId that has either a higher OrderField value or an equal OrderField with a lower Id value. Thus, it effectively retrieves the row with the greatest OrderField within each group.

Optimization

Using the left outer join approach can significantly improve query performance compared to the earlier method involving @Rank and subqueries. The left outer join can leverage an index on (GroupId, OrderField) for optimized access.

Caution regarding @Rank

The initial approach using @Rank may not function as expected due to the fact that @Rank increments continue after the first table is processed. To reset @Rank to zero between tables, you would need to introduce an additional derived table, but this can result in poor optimization.

The above is the detailed content of How to Efficiently Find Minimum and Maximum Values per Group in SQL Without Using Ranking Functions?. For more information, please follow other related articles on the PHP Chinese website!