Forum Discussion
Tom Morgan
Apr 25, 2019MVP
Using top-nested but not sure if I'm doing it right
Hi,
Hope this is an OK place for a Kusto Query language question...
Given some data, where there might be multiple versions of the same data, for instance:
+----+---------+-------+-------+ | ID | Version | Attr1 | Attr2 | +----+---------+-------+-------+ | 1 | 1 | 123 | 10.0 | | 2 | 1 | 123 | 5.0 | | 2 | 2 | 222 | 4.0 | | 3 | 1 | 123 | 5.0 | | 3 | 2 | 222 | 4.0 | | 3 | 3 | 333 | 5.0 | +----+---------+-------+-------+
And, what I want to return are only the latest versions of all the rows, eg:
+----+---------+-------+-------+ | ID | Version | Attr1 | Attr2 | +----+---------+-------+-------+ | 1 | 1 | 123 | 10.0 | | 2 | 2 | 222 | 4.0 | | 3 | 3 | 333 | 5.0 | +----+---------+-------+-------+
Right, now, to achieve that I'm doing:
TableName | top-nested of ID by max(1) , top-nested 1 of Version by max(Version), top-nested of Attr1 by max(1), top-nested of Attr2 by max(1)
It works, but it's not very pretty, and in the real-world there are a lot more columns, so it gets big quick.
Is there a better way to achieve what I'm trying to do?
- Try using "summarize arg_max()" : https://docs.microsoft.com/en-us/azure/kusto/query/arg-max-aggfunction
2 Replies
- Yoni
Microsoft
Try using "summarize arg_max()" : https://docs.microsoft.com/en-us/azure/kusto/query/arg-max-aggfunctionYoni - thank you! Works perfectly. For everyone else, this achieves the same results as my original one, but much cleaner and faster:
TableName | summarize arg_max(Version,*) by ID