CODEWITHSUNDEEP
group-byazure-data-explorerkqlpartition-bykusto-explorer
cmomah
cmomah

Reputation: 315

Get top 1 row of each group using Kusto

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:

DocumentID Status DateCreated
1 S1 8/02/2011
2 S3 8/01/2011
3 S1 8/02/2011

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

Upvotes: 11

Views: 15795

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

You can use the partition operator, or the arg_max() aggregation function.

For example:

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

Or

DocumentStatusLogs
| summarize arg_max(DateCreated, *) by DocumentId

Upvotes: 28

Related Questions