3

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

Allen Harris
  • 53
  • 5

2 Answers2

5

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
Yoni L.
  • 22,627
  • 2
  • 29
  • 48
-1

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
Gary Bushey
  • 19
  • 2
  • This solution is very inefficient (because of the unneeded `join`). Instead, `summarize arg_max(...)` should be used, like Yoni answered. I suggest deleting this answer. – Slavik N Aug 25 '21 at 04:06