Kusto query - sorting by extended column

Question

How should Kusto query on count be adjusted to show the results with correct sequential sorting by 'name' - alphabetical sorting is not appropriate here, as actual sequence of 'name' values is Step F -> Step W -> Step B, etc. Seems that I should map 'name' to extended column "Number" with smth like <Step F == 1, Step W == 2,...> and then add sorting by this column.

timestamp                     name      count
=== ========= ====
01/01/2020, 12:00:00.000 AM   Step W      55
01/01/2020, 12:00:00.000 AM   Step B      44
01/01/2020, 12:00:00.000 AM   Step F      33
01/01/2020, 12:00:00.000 AM   Step D      10

Please help adjusting the query:

tablename 
| where name == "Step D"
or name == "Step F"
or name == "Step B"
or name == "Step W"
| summarize count() by name, bin(timestamp, 1d)

Yoni L. Yoni L. · Accepted Answer · 2020-11-17T00:46:04

If I understand correctly, you're interested in something like this:

let weights = dynamic({"Step F":1, "Step W":2, "Step B": 3, "Step D":4});
datatable(timestamp:datetime, name:string, count:int)
[
    datetime(01/01/2020 12:00:00.000 AM), 'Step W', 55,
    datetime(01/01/2020 12:00:00.000 AM), 'Step B', 44,
    datetime(01/01/2020 12:00:00.000 AM), 'Step F', 33,
    datetime(01/01/2020 12:00:00.000 AM), 'Step D', 10,
]
| order by toint(weights[name]) asc

or, with your query:

let weights = dynamic({"Step F":1, "Step W":2, "Step B": 3, "Step D":4});
tablename 
| where name in("Step D", "Step F", "Step B", "Step W")
| summarize count() by name, bin(timestamp, 1d)
| order by toint(weights[name]) asc

Kusto query - sorting by extended column

1 Answers