Upsolver
Search…
FIRST
Returns the first value of the aggregated key in the stream.

Syntax

FIRST(VALUE)

Arguments

VALUE: An expression of any type.

Returns

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

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

Find the first connection_time for each GROUP_ID and USER_ID:
1
SELECT
2
data.GROUP_ID AS group_id:STRING,
3
data.USER_ID AS user_id:STRING,
4
FIRST(data.CONNECTION_TIME) AS first_data_connection_time:STRING
5
FROM
6
"SAMPLE_DATA_G1U1 - json"
7
GROUP BY
8
data.GROUP_ID,
9
data.USER_ID"
Copied!

Results

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

Dialog