Kusto, retrieving all the rows with maximum values

Question

I want to create a MV based on a query like this ("data" is my table):

let data = datatable(Fruit: string, Color: string, Version: int) [
"Apple", "Red", 1,
"Apple", "Green", 1,
"Pear", "Brown", 1,
"Pear", "Green", 2,
]; 
let data2 = data| summarize max(Version) by Fruit | project-rename 
Version=max_Version;
data  |  lookup kind=inner  (data2) on Version,Fruit

However, the creation MV failed due to:

Cannot create materialized view 'TestMV': Materialized Views query can only contain a table reference and a single summarize operator which must be last (T | where ... | summarize agg1(), agg2()... aggN() by dimension1 ... dimensionM.

How can I simplify the query so I can create MV from it?

It's very similar to arg_max but in my case, I need all the records of the max value.

datatable(Fruit: string, Color: string, Version: int) [
    "Apple", "Red", 1,
    "Apple", "Green", 1,
    "Pear", "Brown", 1,
    "Pear", "Green", 2,
]
| summarize arg_max(Version, *) by Fruit

any suggestions?

Can you add the failed create materialized view statement? – Avnera May 25 '22 at 11:28 — Avnera, May 25 '22 at 11:28

score 2 · Answer 1 · answered May 25 '22 at 11:35

It's not possible to define this query as a materialized view. As the error message says, the materialized view must have a single aggregation at the end of the query, and there's no way to express what you're looking for with these constraints. You can define a materialized view to hold the max version per Fruit, but you'll need to join with the view during query time to get all records.

Kusto, retrieving all the rows with maximum values

1 Answers1