Get top 1 row of each group using Kusto

Question

I have a table which I would like to get the latest entry for each group using Kusto Query Language. Here's the table:

DocumentStatusLogs

ID	DocumentID	Status	DateCreated
2	1	S1	7/29/2011
3	1	S2	7/30/2011
6	1	S1	8/02/2011
1	2	S1	7/28/2011
4	2	S2	7/30/2011
5	2	S3	8/01/2011
6	3	S1	8/02/2011

The table would be grouped by DocumentID and sorted by DateCreated in descending order. For each DocumentID, I want to get the latest status.

My preferred output:

Is there any way to get only the top from each group using KQL?

The pseudo-code GetOnlyTheTop is as follows:

SELECT
  DocumentID,
  GetOnlyTheTop(Status),
  GetOnlyTheTop(DateCreated)
FROM DocumentStatusLogs
GROUP BY DocumentID
ORDER BY DateCreated DESC

Credit: Question adapted from DPP's SQL question: Get top 1 row of each group

score 22 · Accepted Answer · answered Mar 25 '22 at 02:28

22

For example:

DocumentStatusLogs
| partition by DocumentId
(
    top 1 by DateCreated desc
)

Or

DocumentStatusLogs
| summarize arg_max(DateCreated, *) by DocumentId

answered Mar 25 '22 at 02:28

Yoni L.

I just realized that the `partition` operator was enhanced and now supports limitless partitions (and not just 64). Amazing improvement for KQL. Thanks Yoni! :-) – David דודו Markovitz Mar 25 '22 at 11:21
@DavidדודוMarkovitz - it does not actually. Just got the error message `partition operator: exceeded amount of maximum partitions allowed (64)` – cdrrr Jun 19 '23 at 08:45
as mentioned in [the documentation for the `partition` operator](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/partitionoperator), the limit on 64 partitions still exists for the Legacy strategy. you can use a different strategy (e.g. `native`) if required – Yoni L. Jun 19 '23 at 15:57

1 Answers1