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?

Answer 1

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.