Upsolver
Search…
LAST
Returns the last value of the aggregated key in the stream.

Syntax

LAST(VALUE)

Arguments

VALUE: An expression/field of any type.

Returns

Returns a value of last expression for each each key in the aggregation statement. The result type matches the VALUE type.

AVAILABLE IN

Output Type Availablity Aggregate YES Explicit Lookup Table YES Inline Joins\Lookups YES

Example

Data

1
[
2
{
3
"GROUP_ID":"G1",
4
"USER_ID":"U1",
5
"CONNECTION_TIME":"2020-06-26 02:31:29,573"
6
},
7
{
8
"GROUP_ID":"G1",
9
"USER_ID":"U2",
10
"CONNECTION_TIME":"2020-06-26 18:11:45,783"
11
},
12
{
13
"GROUP_ID":"G2",
14
"USER_ID":"Z1",
15
"CONNECTION_TIME":"2020-06-26 23:54:27,687"
16
},
17
{
18
"GROUP_ID":"G2",
19
"CONNECTION_TIME":"2020-07-26 23:54:27,687"
20
},
21
{
22
"GROUP_ID":"G1",
23
"USER_ID":"U2",
24
"CONNECTION_TIME":"2021-07-01 02:31:29,573"
25
},
26
{
27
"GROUP_ID":"G1",
28
"USER_ID":"U1",
29
"CONNECTION_TIME":"2021-07-01 18:11:45,783"
30
},
31
{
32
"GROUP_ID":"G2",
33
"USER_ID":"Z1",
34
"CONNECTION_TIME":"2021-07-01 23:54:27,687"
35
}
36
]
Copied!

Query

List the last connection time by user group and user id:
1
SET
2
DATE_TIME = TO_DATE(data.CONNECTION_TIME);
3
SELECT
4
data.GROUP_ID AS group_id:STRING,
5
data.USER_ID AS user_id:STRING,
6
LAST(data.CONNECTION_TIME) AS last_data_connection_time:STRING
7
FROM
8
"SAMPLE_DATA_G1U1-json"
9
GROUP BY
10
data.GROUP_ID,
11
data.USER_ID
Copied!

Results

1
[
2
{
3
"group_id": "G2",
4
"last_data_connection_time": "2020-07-26 23:54:27,687"
5
},
6
{
7
"group_id": "G2",
8
"last_data_connection_time": "2021-07-01 23:54:27,687",
9
"user_id": "Z1"
10
},
11
{
12
"group_id": "G1",
13
"last_data_connection_time": "2021-07-01 02:31:29,573",
14
"user_id": "U2"
15
},
16
{
17
"group_id": "G1",
18
"last_data_connection_time": "2021-07-01 18:11:45,783",
19
"user_id": "U1"
20
}
21
]
Copied!

FIRST

Dialog