Application Insights analytics - Select first value per category

Question

I want do an equivalent of the following SQL query -

(roughly)

SELECT 
    Name,
    application_Version
    Rank() OVER (PARTITION BY application_Version ORDER BY CountOfEventNamePerVersion)
FROM
    customEvents

Assuming I get the CountOfCompanyPerVersion field easily. I want to do the same using AIQL but I'm not able to do this. Here's a query that I am tried -

customEvents
| summarize count() by name, application_Version
| project name, application_Version, count_
| summarize x = count(count_) by application_Version
| where x = count_

Basically I want to get the most common Name per application_Version. How can I do this?

For people not that advanced in SQL =) Can you please elaborate on what you'd like to achieve? Pick the top Name (based on count) for each application_Version and print "application_Version, name"? — ZakiMa
@ZakiMa yes that's exactly I wanted to say (in the last statement of my question, I tried to say so). =) — Nikhil Girraj

ZakiMa ZakiMa · Accepted Answer · 2018-02-26T09:37:44

arg_max should do the trick:

customEvents
| summarize count() by Name, application_Version
| summarize arg_max(count_, Name) by application_Version
| order by application_Version 
| project application_Version, Name=max_count__Name

Application Insights analytics - Select first value per category

1 Answers