Forum Discussion
Last 3 values for multiple categories
All, I'm trying to get the last three values of a dataset by a particular category, and I am having trouble figuring out the best way to do this. I have the following columns: TimeGenerated Mo...
let YourDataTable = datatable(TimeGenerated: datetime, SiteName: string, MonitorName: string, Availability: int) [
datetime("2018-10-05T06:44:37.353"), "Houston", "Test1", 0,
datetime("2018-10-05T06:34:37.353"), "Houston", "Test1", 1,
datetime("2018-10-05T06:24:37.353"), "Houston", "Test1", 0,
datetime("2018-10-05T05:00:00.000"), "Houston", "Test1", 999,
datetime("2018-10-05T06:31:00.000"), "Houston", "Test2", 1,
datetime("2018-10-05T06:21:00.000"), "Houston", "Test2", 1,
datetime("2018-10-05T06:11:00.000"), "Houston", "Test2", 1,
datetime("2018-10-05T05:00:00.000"), "Houston", "Test2", 999,
datetime("2018-10-05T06:51:00.000"), "Houston", "Test3", 0,
datetime("2018-10-05T06:41:00.000"), "Houston", "Test3", 0,
datetime("2018-10-05T06:31:00.000"), "Houston", "Test3", 1,
datetime("2018-10-05T05:00:00.000"), "Houston", "Test3", 999,
datetime("2018-10-05T06:38:00.000"), "Los Angeles", "Test1", 1,
datetime("2018-10-05T06:28:00.000"), "Los Angeles", "Test1", 1,
datetime("2018-10-05T06:18:00.000"), "Los Angeles", "Test1", 1,
datetime("2018-10-05T05:00:00.000"), "Los Angeles", "Test1", 999,
datetime("2018-10-05T06:55:00.000"), "Los Angeles", "Test2", 0,
datetime("2018-10-05T06:45:00.000"), "Los Angeles", "Test2", 0,
datetime("2018-10-05T06:35:00.000"), "Los Angeles", "Test2", 1,
datetime("2018-10-05T05:00:00.000"), "Los Angeles", "Test2", 999
];
let TopN = 3;
YourDataTable
| order by TimeGenerated desc
| extend row = row_number()
| as T
| join kind=leftouter (
T | summarize minRow = min(row) by SiteName, MonitorName
) on SiteName, MonitorName
| where row < minRow + TopN
| project TimeGenerated, SiteName, MonitorName, Availability
| order by SiteName asc, MonitorName asc, TimeGenerated desc