How could I do a filtered join in Kusto?

How could I do a filtered join in Kusto?

E.g. I would like to do the following join:

| 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"
| 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


Leave a Reply