Get Other columns based on max of one column in Kusto

Question

I am trying to write a Kusto query to find record who has max value in column grouped by another column but also requires 3rd(remaining) columns with it.

Let there be three columns A(timestamp) B(impvalue: number) and C (anothervalue:string). I need to get records grouped by C with max timestamp and its corresponding B column too.

In Sql, I am well aware how to do it using self join. I am new to Kusto, I tried few combination with summarize, join and top operator but wasn't able to make it work.

Example:

Output:

Answer 1

you can use the arg_max() aggregation function: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/arg-max-aggfunction.

For example:

datatable(A:datetime, B:long, C:string)
[
    datetime(2020-08-20 12:00:00), 50,  "abc",
    datetime(2020-08-20 12:10:00), 30,  "abc",
    datetime(2020-08-20 12:05:00), 100, "abc",
    datetime(2020-08-20 12:00:00), 40,  "def",
    datetime(2020-08-20 12:05:00), 120, "def",
    datetime(2020-08-20 12:10:00), 80,  "def",
]
| summarize arg_max(A, *) by C

C	A	B
abc	2020-08-20 12:10:00.0000000	30
def	2020-08-20 12:10:00.0000000	80