MAX_EACH

Collects the maximum value provided in VALUE for each GROUP.

Syntax

MAX_EACH([MAX VALUES, ]GROUP, VALUE)

Arguments

GROUP: A grouping field VALUE: An expression of a NUMERIC type that can be ordered MAX VALUES: The maximum number of groups. Groups beyond this amount will be discarded (Defaults to 2147483647). In some cases, depending on the data distribution, discarding groups via MAX VALUES may cause some data to be discarded from groups that would otherwise have been returned. MAX VALUES is optional. If it is omitted there is, in effect, no limit.

Returns

An array of key-value pairs where the key type matches the GROUP type and the value corresponds to the VALUE type (numeric).

Notes

"EACH" data must be hierarchical. MAX_EACH only appears as an option in the ADD AGGREGATE dialog when the source data is hierarchical. Trying to aggregate MAX_EACH, when the data is tabular, is likely to result in an error message: MAX_EACH can produce output in an array.

AVAILABLE IN

Output Type Availablity Aggregated Outputs NO Explicit Lookup Table YES Inline Joins\Lookups NO

Example

Data

[
   {
      "user":"A01",
      "level":1,
      "score":25
   },
   {
      "user":"A01",
      "level":2,
      "score":48
   },
   {
      "user":"A01",
      "level":2,
      "score":81
   },
   {
      "user":"B02",
      "level":1,
      "score":23
   },
   {
      "user":"B02",
      "level":1,
      "score":29
   }
]

Query

Find the highest score for each level, and sort the results by user:

SELECT
   data.user AS user:STRING,
   MAX_EACH(data.level, data.score) AS max_each_data_level__data_score 
FROM
   "MAX_SAMPLE_DATA - json" 
GROUP BY
   data.user

Results

{
   "max_each_data_leveldata_score":[
      {
         "key":1,
         "value":25
      },
      {
         "key":2,
         "value":81
      }
   ],
   "user":"A01"
}{
   "max_each_data_leveldata_score":[
      {
         "key":1,
         "value":29
      }
   ],
   "user":"B02"
}

Related Functions

MAX MAX_BY MAX_TIME_SERIES

Dialog