Reputation: 31
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?
Upvotes: 3
Views: 931
Reputation: 2744
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.
Upvotes: 2