How to sort by second column in KQL regardless of column name

Question

I used pivot in KQL, now I have dynamically named columns and I would like to sort by the second one.

The table looks like this

Product	11.2023	10.2023
Name 1	80	50
Name 2	24	71
Name 3	33	60

I want to sort by the "11.2023" column (second column overall), but this column is not always the same, however, I want to always use the same query.

I thought something like

T
| order by 1 desc

or

T
| sort by 1 desc

would work but it just doesn't sort.

Answer 1

As per this sort operator,

It's not mentioned anywhere in the documentation that we can sort by ordinal position if we use query like sort by 1 desc. So, it's not possible with the above query.

It will give the same table result with your table.

These are my observations when I tried various methods:

I tried the following method where first I have stored the second column name in a variable using getschema.

let a = datatable(Product:string, ['11.2023']:int, ['10.2023']:int)
[
    "Name 3",80,50,
    "a",24,71,
    "Name 2",33,60
];
let s =a | getschema;
let b= toscalar(s
| extend col= iif(ColumnOrdinal==0,ColumnName,'')
| summarize max(col)
);
a
| order by column_ifexists(b,'') desc;

But later when I tried to order by that variable, I got an error saying column_ifexists(): argument #1 must be string literal.

The column_ifexists() methods first argument should be only a string literal but here the string variable b was calculated from another expression.

I have tried to build a KQL string query and tried to execute that, but currently KQL doesn't have any equivalent functions like exec() of SQL.

So, currently it might not be possible to achieve your requirement. You can raise your idea as a feature request here.