How could I do a filtered join in Kusto?
E.g. I would like to do the following join:
a
| join kind=leftouter b on id
but also, if a
has more than one matching rows in b
I would like to pick only a single row from b
with the earliest timestamp
.
>Solution :
let a = datatable(id:int)[1, 2, 3];
let b = datatable(id:int, timestamp:datetime, extra_info:string)
[
1, datetime(2023-02-28), "Tic"
,1, datetime(2023-02-07), "Tac"
,1, datetime(2023-02-19), "Toe"
,2, datetime(2023-02-10), "Eeny"
,2, datetime(2023-02-07), "Meeny"
,2, datetime(2023-02-12), "Miny"
,2, datetime(2023-02-15), "Moe"
,3, datetime(2023-02-01), "Foo"
,3, datetime(2023-02-02), "Bar"
];
a
| join kind=leftouter (b | summarize arg_min(timestamp, *) by id) on id
id | id1 | timestamp | extra_info |
---|---|---|---|
3 | 3 | 2023-02-01T00:00:00Z | Foo |
1 | 1 | 2023-02-07T00:00:00Z | Tac |
2 | 2 | 2023-02-07T00:00:00Z | Meeny |