CODEWITHSUNDEEP
azure-data-explorerkql
nrod88
nrod88

Reputation: 27

Query table to show the max date in Kusto KQL Azure Data Explorer

How would be a query in Kusto in order to query various fields, by filtering the data for its max date column only. ex: see only on the query data for 09-08-2023

something like |where datecolumn == max(datecolumn)

example:

Animals   Color      Datecolumn
cat        orange     01-08-2023
dog        black      03-08-2023
bird       yellow     09-08-2023
dog        white      09-08-2023

I want to return all the columns and field selected and only filter the data by its max(date) which in this case is for now 09-08-2023 (2 rows in the example), but overall for the future I want the max/lates datecolumn value always.

In the practice I'm querying more columns and doing some extend functions in my query but I just want to filter the data by its latest date always

Upvotes: 0

Views: 1019

Answers (1)

Aswin
Aswin

Reputation: 7156

Code

let Table1 = datatable(Animals:string, Color:string, Datecolumn:datetime)
[
'cat', 'orange', datetime(2023-08-01),
'dog', 'black', datetime(2023-08-03),
'bird', 'yellow', datetime(2023-08-09),
'dog', 'white', datetime(2023-08-09)
];
let max_date = toscalar(Table1| summarize MaxDate = max(Datecolumn));
Table1
| where Datecolumn == max_date

The query first create a scalar value called max_date using the let statement and the toscalar function. The toscalar function returns a single scalar value from a table, which in this case is the maximum date value from the Datecolumn field in Table1. Then the query filters the data in Table1 by the maximum date value using the where Datecolumn matches with max_date value.

Output:

Animals Color Datecolumn
bird yellow 2023-08-09T00:00:00Z
dog white 2023-08-09T00:00:00Z

Upvotes: 1

Related Questions