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
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 |