SQL: ORDER BY COUNT() throws error

[EthanStrider]

Elasticsearch version (v6.3.1):

Description of the problem including expected versus actual behavior:

I’m trying to list the counts of each city in descending order.
For example, if Paris occurred 100 times in the database, London occurred 25 times, and Sydney 3, then I would like the output to be listed with Paris first, then London, then Sydney.

name      count
Paris     100
London    25
Sydney    3

However, the following SQL query throws an error:

POST /_xpack/sql?format=txt
{
    "query": "SELECT geoip.city_name AS name, COUNT(geoip.city_name) AS count FROM \"cyclops-sessions\" GROUP BY geoip.city_name ORDER BY count DESC"
}

Error

{
  "error": {
    "root_cause": [
      {
        "type": "verification_exception",
        "reason": "Found 1 problem(s)\nline 1:123: Cannot order by non-grouped column [COUNT(1)], expected [geoip.city_name]"
      }
...
  "status": 400
}

[elasticmachine]

Pinging @elastic/es-search-aggs

[astefan]

While this is a valid and probably frequently used type of functionality, in ES-SQL this is not possible (at the moment) due to how the composite aggregation (used behind the scene to perform the grouping and ordering) works: it can only order by the key (the value). I do have a concern, though, regarding the error message. More about this in this issue.

[madminions]

ES v - 6.4.1
If you do like this, no error is thrown
POST /_xpack/sql?format=txt
{
"query": "SELECT name,count from (SELECT geoip.city_name AS name, COUNT(geoip.city_name) AS count FROM "cyclops-sessions" GROUP BY geoip.city_name) ORDER BY count DESC"
}

But still it doesn't return the query sorted in DESC order.
Any workaround for this query ?

If possible i would like to work on this new feature.

[paulcarey]

Having the ability to order by a count would be very useful indeed. The following comment implies that this might be possible at some point in future (emphasis mine).

While this is a valid and probably frequently used type of functionality, in ES-SQL this is not possible (at the moment)

But this issue has a won't fix label.

Does this mean there are no near-term plans to support this feature, but that it's not entirely ruled out?

[costin]

There's a separate issue (#35118) that tracks this feature which is currently being investigated. It's best to monitor that for progress.

[costin]

As FYI, this feature is now supported and merged into 7.x and upcoming 6.7.

[kkmathigir]

I am in need of this feature big time. Is there a patch that can be made available for 6.6? This is quite helpful for our Canvas implementation. Or any workaround in 6.6?

[abubogale342]

I have almost similar issue. count is working fine for me. But I got one problem. My query is like below:

select * from (select street, count(street) as counts, min(grocery) as grocery, min(school) as school, (min(grocery) + min(school)) as score from geo_address group by 1) t order by grocery

I have geo_address index and I am creating column on the fly which is score that is sum of grocery column and school column.
Ther result :

Now it is sorted based on the grocery but I want to sort based on score. when I try to sort by it I get an error : - Cannot order by non-grouped column [score]. can anyone please give me any workaround to solve this problem?