Kusto: Filter results to latest record for each ID

user_8593689 0
Viewed 4757

Suppose I have a table like the following in Azure Data Explorer:

let data = datatable(id:int, dateTime:datetime)
[
    1,"2021-03-03",
    1,"2021-03-04",
    1,"2021-03-12",
    2,"2021-03-04",
    2,"2021-03-04",
    2,"2021-03-07"
]

I want a Kusto Query Language query that will find the record with the latest datetime for each id. I.e., I want to the query to return the following records:

id dateTime
2 2021-03-07 00:00:00.0000000
1 2021-03-12 00:00:00.0000000

I looked at this question but in this case the OP wanted only one record, which was the max for the entire table. I want to get back the record with the latest datetime for each id.

How can I do this?

(This sample table is for illustrative purposes, the real data I am dealing with is much larger and has many IDs, and is unsorted.)

kql azure-data-explorer
1 Answers

I want a Kusto Query Language query that will find the record with the latest datetime for each id

If you wish to only get the maximum datetime value for each id, you should use the max() aggregation function:

datatable(id:int, dateTime:datetime, message:string)
[
    1,"2021-03-03", "a",
    1,"2021-03-04", "b",
    1,"2021-03-12", "c",
    2,"2021-03-04", "d",
    2,"2021-03-04", "e",
    2,"2021-03-07", "f"
]
| summarize max(dateTime) by id
id max_dateTime
2 2021-03-07 00:00:00.0000000
1 2021-03-12 00:00:00.0000000

Or, if you wish to preserve other columns of the maximized record, you should use the arg_max() aggregation function:

datatable(id:int, dateTime:datetime, message:string)
[
    1,"2021-03-03", "a",
    1,"2021-03-04", "b",
    1,"2021-03-12", "c",
    2,"2021-03-04", "d",
    2,"2021-03-04", "e",
    2,"2021-03-07", "f"
]
| summarize arg_max(dateTime, *) by id
id dateTime message
2 2021-03-07 00:00:00.0000000 f
1 2021-03-12 00:00:00.0000000 c
user_78618070
Related