CODEWITHSUNDEEP
azure-data-explorerkql
swordHeart
swordHeart

Reputation: 51

Kusto how to select the latest record with the same id in a group of daily records

We query timeseries data for the last 7 days. One user (defined by user id) may send several records in one day.

We want to get the latest record of that day per each user. How to write it in Kusto?

Current table schema is like this: example data table with columns UserId, time, Column1, Column2, Column3, Column4 and 6 records

Upvotes: 1

Views: 961

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

If I understand your question correctly, you can use summarize arg_max() for that:

datatable(uid:int, dt:datetime, c1:int, c2:bool, c3:string, c4:int)
[
    1, datetime(2020-11-14 00:00:10), 12, true, 'exception', 2,
    1, datetime(2020-11-14 00:00:20), 21, false, 'well', 4,
    2, datetime(2020-11-07 00:00:08), 1, true, 'well', 5,
    3, datetime(2020-11-08 00:03:20), 3, false, 'exception', 2,
    2, datetime(2020-11-08 00:00:20), 13, false, 'well', 24,
]
| summarize arg_max(dt, *) by uid, startofday(dt) 

    uid dt                      dt1                     c1  c2      c3   c4
    2   2020-11-07T00:00:00Z    2020-11-07T00:00:08Z     1  true    well        5
    3   2020-11-08T00:00:00Z    2020-11-08T00:03:20Z     3  false   exception   2
    2   2020-11-08T00:00:00Z    2020-11-08T00:00:20Z    13  false   well       24
    1   2020-11-14T00:00:00Z    2020-11-14T00:00:20Z    21  false   well        4

Upvotes: 2

Related Questions