Kibana SQL question - subselect

Hi, I am trying to get the latest messages and then sort these messages based on one of their fields(i.e. name in this case). I tried the following. It ran but the sorting didn't happen. Any idea of what I am doing wrong? Thanks in advance for any help.

select tmp.name, tmp.overallStatus from (
SELECT name, overallStatus FROM "sites"
ORDER BY "@timestamp" DESC
LIMIT 80) tmp
ORDER BY tmp.name asc

Which part of the sorting didn't happen? Timestamp, name, or both?

I attempted to reproduce this using Kibana's sample data and I did not have any issues with subselect sorting like this.

The above order by didn't happen.

Interesting. Can you try flattening your query? I know this isn't exactly what you have written with the limit, but it's close:

SELECT name, overallStatus FROM "sites"
ORDER BY "@timestamp" DESC, name ASC
LIMIT 80

Yes, they are working without selecting from the subselect but the result is not the same as the one as the original one.

Currently only simple subqueries that can be flattened are supported, see: SQL Limitations | Elasticsearch Reference [7.11] | Elastic

What you want to achieve is to have a 1st level ordering, limit the results and at a 2nd level re-order those limited results by another sorting column, which is semantically different than flattening the order by (order by 1, 2 means order by 1 and for equalities on 1 order those entries by 2).

To sum up, what you want to do is not currently supported by ES-SQL.

1 Like

@matriv Thanks for confirming.

1 Like