How to order items in makeset?

[imeya]

Is there a way to order items in makeset()?

There are some confusion in docs.

In this doc -> Returns section, it says that
: Returns a dynamic (JSON) array of the set of distinct values that Expr takes in the group. The array's sort order is undefined.

But in another doc, it says that: Like makelist, makeset also works with ordered data and will generate the arrays based on the order of the rows that are passed into it.

I also tried some methods, but no ordering in the makeset() result.

method 1:

let topCities =
requests
   | distinct client_City 
   | order by client_City asc;

topCities
| summarize makeset(client_City)

method 2:

requests
   | distinct client_City 
   | order by client_City asc
   | summarize makeset(client_City) 

And the result like this: ["","Hong Kong","Chicago","Boydton","Campinas","Des Moines"].
but I want the items should be ordered like this: ["","Boydton","Campinas","Chicago","Des Moines","Hong Kong"]

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: aa4abe74-27fa-93a8-ffc6-a2d75a8291da
• Version Independent ID: e37207e6-a781-e2b1-7fee-c020568f3b41
• Content: Analytics - the powerful search and query tool of Azure Application Insights
• Content Source: articles/azure-monitor/app/analytics.md
• Service: application-insights
• GitHub Login: @mrbullwinkle
• Microsoft Alias: mbullwin

[MohitVerma-MSFT]

Hi @imeya ,
Thank you for reaching out. Could you please try below syntax:-

requests | summarize makeset(client_City) by client_City | distinct client_City | order by client_City asc

[imeya]

thank you. But what I want is that the result is a set(in 1 row like ["a","b","c"]), not in many rows like in row1: a, in row 2, b.

[MohitVerma-MSFT]

Hi @imeya ,

I tried to reproduce the issue with the steps you mentioned. here are my findings;
Correct statement with regards to makeset would be , it would returns a dynamic (JSON) array of the set of distinct values that Expr takes in the group. The array's sort order is undefined.
Meanwhile we are working on to correct this document.

Though for a workaround for this , you can use blow query -

requests | summarize makeset(client_City) by client_City | distinct client_City | order by client_City asc

which will give you result in rows and later you can concat the result in one row(comma seperated).

[imeya]

thanks again. then how to concat the result in one row, with comma seperated?

[MohitVerma-MSFT]

Hi @imeya ,

Try this code , it should give you what you are looking for.

requests | summarize makeset(client_City) by client_City | distinct client_City | order by client_City asc | summarize makelist(client_City)

Hope it helps.

[imeya]

@MohitVerma-MSFT, thank you very much. Please correct the document. And actually, we can directly use makelist() which supports ordering.

[MohitVerma-MSFT]

Yes, Makelist preserve the ordering of the input items. I have corrected the document and it will be published soon.
I am closing the issue.