Kibana SQL question - subselect

Elastic Stack Kibana
#1

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

#2

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.

#3

The above order by didn't happen.

#4

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
#5

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

#6

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
#7

@matriv Thanks for confirming.

1 Like