MAX_BY

The value correlating to the maximum sort in the time window.

‌Syntax

MAX_BY(VALUE, SORT)‌

Arguments

VALUE: An expression of any type. SORT: An integer type that can be ordered. ‌

Returns

The result type matches the VALUE type. If the maximum value is not unique, the first VALUE in the set will be returned.

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

SELECT
   MAX_BY(data.user, data.score) AS max_by_data_user__data_score:STRING,
   MAX(data.score) AS max_data_score:BIGINT,
   data.level AS level:BIGINT 
FROM
   "MAX_SAMPLE_DATA - json" 
GROUP BY
   data.level

Results

{
   "level":2,
   "max_data_score":81,
   "max_by_data_userdata_score":"A01"
}{
   "level":1,
   "max_data_score":29,
   "max_by_data_userdata_score":"B02"
}

Related Functions

MAX() MAX_EACH() MAX_TIME_SERIES MIN MIN_BY

Dialog