Kusto query get the X in each Y with max Z

0
votes

I'm trying to write a Kusto query to get the [x] in each [y] with the most [z]. To be more specific, I'm querying the Azure Data Explorer sample table Covid to find the state with the most deaths in each country. I've tried this:

Covid19
| where State <> ""
| summarize deaths = max(Deaths) by State, Country
| project Country, State, deaths
| order by deaths

which runs, but produces multiple states from the same country. I also tried this:

Covid19
| where State <> ""
| summarize deaths = max(Deaths) by State, Country
| summarize deths = max(deaths) by State
| project Country, State, deths

But then I get an error on the last line "The name 'Country' does not refer to any known column...".

I also tried this:

Covid19
| where State <> ""
| summarize deaths = max(Deaths) by State, Country
| summarize deths = max(deaths) by Country
| project Country, deths

Which seems to produce correct results, but then I can't display the state. So it seems like I'm getting a runaround where I have either too much info or not enough.

Actual results from first query:

Actual results

Expected results:

Expected Results

2
azure-data-explorerkql

2 Answers

2
votes

you could try using the top-nested operator: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/topnestedoperator

Covid19
| where isnotempty(State)
| top-nested of Country by max(1), top-nested 1 of State by max(Deaths)
| project Country, State, deaths = aggregated_State
| order by deaths

or, if you want a more "natural"/"intuitive" syntax (as you've stated in your comment), you could use arg_max(): https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/arg-max-aggfunction

Covid19
| where isnotempty(State)
| summarize arg_max(deaths, *) by Country
| order by deaths
0
votes

I found out I can use arg_max for this, which I think is the simplest way to go

Covid19
| where isnotempty(State)
| summarize arg_max(Deaths, State) by Country

With specified output order:

Covid19
| where isnotempty(State)
| summarize arg_max(Deaths, State) by Country
| order by Deaths
| project Country, State, Deaths