STRING_MAX_EACH

Collects the maximum value provided in VALUE for each GROUP.

Syntax

STRING_MAX_EACH ([MAX VALUES, ]GROUP, VALUE)

Arguments

MAX VALUES: An expression of INTEGERtype for how many values to return. The default is adequate in most cases. MAX_VALUES is a "soft" limit, which may be used in order to optimize response times. GROUP: A grouping field. VALUE: An expression of a STRING type that can be ordered.

Returns

An array of pairs with a key of type GROUPand a corresponding value of type STRING.

Example

Data

[
   {
      "GROUP_ID":"G1",
      "USER_ID":"U1",
      "CONNECTION_TIME":"2020-06-26 02:31:29,573"
   },
   {
      "GROUP_ID":"G1",
      "USER_ID":"U2",
      "CONNECTION_TIME":"2020-06-26 18:11:45,783"
   },
   {
      "GROUP_ID":"G2",
      "USER_ID":"Z1",
      "CONNECTION_TIME":"2020-06-26 23:54:27,687"
   },
   {
      "GROUP_ID":"G2",
      "CONNECTION_TIME":"2020-07-26 23:54:27,687"
   },
   {
      "GROUP_ID":"G1",
      "USER_ID":"U2",
      "CONNECTION_TIME":"2021-07-01 02:31:29,573"
   },
   {
      "GROUP_ID":"G1",
      "USER_ID":"U1",
      "CONNECTION_TIME":"2021-07-01 18:11:45,783"
   },
   {
      "GROUP_ID":"G2",
      "USER_ID":"Z1",
      "CONNECTION_TIME":"2021-07-01 23:54:27,687"
   }
]

Query

When was the most recent entry for each group (serverIp):

SET
   DATE_TIME = TO_DATE(data.CONNECTION_TIME);
SELECT
   STRING_MAX_EACH(data.GROUP_ID, data.CONNECTION_TIME) AS string_max_each_data_group_id__data_connection_time 
FROM
   "SAMPLE_DATA_G1U1 - json"

Results

{
   "string_max_each_data_group_id__data_connection_time":[
      {
         "key":"G1",
         "value":"2021-07-01 18:11:45,783"
      },
      {
         "key":"G2",
         "value":"2021-07-01 23:54:27,687"
      }
   ]
}

Related Functions

STRING_MIN_EACH

Dialog