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 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.)
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 |