“order by” translate error

[jjzhang4]

Hi，
I want to order by field ‘’num‘’ in SQL statement, but it doesn't work. The ordered field is group by key “release”.
sql statement：
{"query":"select release,num from (select release,count(distinct id) as num from table group by release) as test1 order by num ASC "}
the translated dsl：
{"size":0,"_source":false,"stored_fields":"none","aggregations":{"groupby":{"composite":{"size":1000,"sources":[{"773":{"terms":{"field":"release","missing_bucket":false,"order":"asc"}}}]},"aggregations":{"780":{"cardinality":{"field":"id"}}}}}}
I want dsl：
{"size":0,"_source":false,"stored_fields":"none","aggregations":{"groupby":{"composite":{"size":1000,"sources":[{"773":{"terms":{"field":"release","missing_bucket":false,"order":{"id":"asc"}}}}]},"aggregations":{"780":{"cardinality":{"field":"release"}}}}}}

[elasticmachine]

Pinging @elastic/es-search

[matriv]

Hello @jjzhang4,

This is a known missing functionality, currently one cannot order by any aggregate function (count, avg, max, etc.) but only by the grouping key(s). The issue is tracked here: #35118