CODEWITHSUNDEEP
kqlazure-data-explorerkusto-explorer
PorgtheEaten
PorgtheEaten

Reputation: 194

How to order a set in Kusto KQL/Kusto/Data Explorer

When I apply an 'order by' command to a query prior making sets the order does not stick. How do I alphabetize the resulting set in the query below:

datatable (CaseNumber:string, AmendmentNumber:string, AmendmentNumberRaw:string, AmendmentType:string)
[
  '5-5-00a000b113z', 'T155', 'T155', 'Non-CTM'
, '5-5-00a000b113z', 'A156', 'A156', 'Non-CTM'
, '5-5-00a000b113z', 'M157', 'M157', 'Non-CTM'
]
| sort by AmendmentNumber asc
| summarize make_set(AmendmentNumber) by CaseNumber

Result:

5-5-00a000b113z ["T155","A156","M157"]

Expected Result:

5-5-00a000b113z ["A156","M157","T155"]

Upvotes: 1

Views: 512

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

for sorting a collection, you could use either of the following functions:

for example:

datatable (CaseNumber:string, AmendmentNumber:string, AmendmentNumberRaw:string, AmendmentType:string)
[
  '5-5-00a000b113z', 'T155', 'T155', 'Non-CTM'
, '5-5-00a000b113z', 'A156', 'A156', 'Non-CTM'
, '5-5-00a000b113z', 'M157', 'M157', 'Non-CTM'
]
| summarize arr = make_set(AmendmentNumber) by CaseNumber
| extend sorted_asc = array_sort_asc(arr), sorted_desc = array_sort_desc(arr)
CaseNumber arr sorted_asc sorted_desc
5-5-00a000b113z [
"T155",
"A156",
"M157"
]		 [
"A156",
"M157",
"T155"
]		 [
"T155",
"M157",
"A156"
]

Upvotes: 2

Related Questions