How to use a GroupAggregator to Return Rows That Have the Minimum Value for Each Given Group? (Doc ID 2045791.1)

Last updated on AUGUST 25, 2015

Applies to:

Oracle Coherence - Version 3.7.1 to 12.1.3.0.2 [Release AS10g to 12c]
Information in this document applies to any platform.

Goal

The example cache has the following fruit objects:

Type Variety Price
Apple Gala 2.79
Apple Fuji 0.24
Apple Limbertwig 2.87
Orange Valencia 3.59
Orange Navel 9.36
Pear Bradford 6.05
Pear Bartlett 2.14
Cherry Bing 2.55
Cherry Chelan 6.33

The goal is to return the SQL equivalent of a correlated sub-query, that is return a result set that is grouped by type and contains the entry (and all attributes) that has the minimum (price), as shown in this table:

Type Variety Price
Apple Fuji 0.24
Orange Valencia 3.59
Pear Bartlett 2.14
Cherry Bing 2.55

This is a simplistic example, in the real world each object might have many more attributes that need to be returned, and which aren't part of the grouping or aggregation.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms