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, Feb 26 '18 at 09:21
@ZakiMa yes that's exactly I wanted to say (in the last statement of my question, I tried to say so). =) — Nikhil Girraj, Feb 26 '18 at 09:32

ZakiMa · Accepted Answer · 2019-04-27T01:01:59.603

2

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

edited Apr 27 '19 at 01:01

answered Feb 26 '18 at 09:37

ZakiMa

5,637
1
24
48

This gets me my intended results. But did you really had to order the intermediate results? – Nikhil Girraj Feb 26 '18 at 09:58
No, you can safely remove intermediate "order by". Not needed. – ZakiMa Feb 26 '18 at 18:01
Updated & removed extra "order by" – ZakiMa Feb 26 '18 at 18:02
1

function is arg_max now – sgriffin Apr 27 '19 at 00:32
Thank you! Updated both the name of a function and a link. – ZakiMa Apr 27 '19 at 01:02

Application Insights analytics - Select first value per category

1 Answers1