Elasticsearch 7.0 SQL ACCESS, Priority of multiple order by commands

Elastic Stack Elasticsearch
1

This is the query I'm trying below.
SELECT "hour", "gender", SUM("aggrCount") AS "cnt" FROM "my.index" WHERE "batchDate" < XXX AND \"batchDate\" >= XXX AND "hour" in ( 10, 11, 12, 13, 14) GROUP BY "hour", "gender" **ORDER BY "hour" ASC, "cnt" DESC**

Here is described as REST API.
POST /_sql?format=json
{
"query": "SELECT "hour", "gender", SUM("aggrCount") AS "cnt" FROM "my.index" WHERE "batchDate" < XXX AND "batchDate" >= XXX AND "hour" in ( 10, 11, 12, 13, 14) GROUP BY "hour", "gender" ORDER BY "hour" ASC, "cnt" DESC"
}

I indicated order by commands using two values of "hour" and "cnt", but the result was ordered by "cnt" first :
{"columns":[{"name":"hour","type":"long"},{"name":"gender","type":"text"},{"name":"cnt","type":"long"}],"rows":[[10,"f",46.0],[10,"m",43.0],[11,"m",16.0],[12,"m",6.0],[11,"f",2.0],[12,"f",2.0],[14,"m",2.0],[13,"m",1.0]]}

And It works in the same way even if the order by commands are swapped into the reverse order (ORDER BY "cnt" DESC, "hour" ASC).
Is there any way to specify the priority of multiple order by commands?

2

Hi @c81b4c93b1f70af5398f,

I think you stumbled upon this bug: https://github.com/elastic/elasticsearch/issues/42851.
Lukily, it was fixed yesterday and the fix should be available in 7.1.2 as the earliest version. See this PR for the complete list of versions where the fix will be in.

1 Like
3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.