CODEWITHSUNDEEP
azure-data-explorerkqlappinsightskusto-explorer
Allen Harris
Allen Harris

Reputation: 53

Get Other columns based on max of one column in Kusto

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:

enter image description here

Output:

enter image description here

Upvotes: 3

Views: 2959

Answers (2)

Yoni L.
Yoni L.

Reputation: 25895

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

Upvotes: 5

Gary Bushey
Gary Bushey

Reputation: 19

This isn't the most elegant solution, but it works:

let X = datatable (a: string, b: int, c: string) [
"8/24/2021, 12:40:00.042 PM", 50, "abc",
"8/24/2021, 12:40:10.042 PM", 30, "abc",
"8/24/2021, 12:40:05.042 PM", 100, "abc",
"8/24/2021, 12:40:00.042 PM", 40, "def",
"8/24/2021, 12:40:05.042 PM", 120, "def",
"8/24/2021, 12:40:10.042 PM", 80, "def"
];
X
| summarize Answer = max(a)
| join X on $left.Answer == $right.a
| project a,b,c

Upvotes: -1

Related Questions