How can I filter out all elements except the newest ones?

user_675738 0
Viewed 156

I want to do something seemingly simple but am struggling to determine how do it in KQL. Say you have a result set with a DateTime column. I want a Kusto query that only keeps rows that have the most recent DateTime.

So for a result set like:

Person Ingestion DateTime
Bob 11/12/2021 9 AM
Sam 11/12/2021 10 AM
William 11/12/2021 11 AM
Kate 11/12/2021 3 PM
Aria 11/12/2021 3 PM
Ben 11/9/2021 4 AM

I want a query that would remove all rows except for Kate and Aria since they both have the latest DateTime in the result set.

I've been trying summarize max() which seems to group the DateTimes but not filter out the older ones. Is there a way to order and then filter?

kql
1 Answers

you can have a sub-query that calculates the scalar value equal to the max datetime value, then filter using that scalar value.

for example:

datatable(Person:string, IngestionDateTime:datetime)
[
    'Bob', datetime(11/12/2021 09:00),
    'Sam', datetime(11/12/2021 10:00),
    'William', datetime(11/12/2021 11:00),
    'Kate', datetime(11/12/2021 15:00),
    'Aria', datetime(11/12/2021 15:00),
    'Ben', datetime(11/9/2021 04:00)
]
| as T
| where IngestionDateTime == toscalar(T | summarize max(IngestionDateTime))
Person IngestionDateTime
Kate 2021-11-12 15:00:00.0000000
Aria 2021-11-12 15:00:00.0000000
user_78618070
Related