SQL Statement Syntaxedit

SELECT * FROM test_emp ORDER BY emp_no;

ORDER BYedit

Elasticsearch supports ORDER BY for consistent ordering. You add any field in the index that has doc_values or SCORE() to sort by _score. By default SQL sorts on what it considers to be the most efficient way to get the results.

So sorting by a field looks like:

POST /_xpack/sql?format=txt
{
    "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
}

which results in something like:

     author      |        name        |  page_count   | release_date
-----------------+--------------------+---------------+------------------------
Peter F. Hamilton|Pandora's Star      |768            |2004-03-02T00:00:00.000Z
Vernor Vinge     |A Fire Upon the Deep|613            |1992-06-01T00:00:00.000Z
Frank Herbert    |Dune                |604            |1965-06-01T00:00:00.000Z
Alastair Reynolds|Revelation Space    |585            |2000-03-15T00:00:00.000Z
James S.A. Corey |Leviathan Wakes     |561            |2011-06-02T00:00:00.000Z

For sorting by score to be meaningful you need to include a full text query in the WHERE clause. If you include multiple full text queries in the WHERE clause then their scores will be combined using the same rules as Elasticsearch’s bool query. Here is a simple example:

POST /_xpack/sql?format=txt
{
    "query": "SELECT SCORE(), * FROM library WHERE match(name, 'dune') ORDER BY SCORE() DESC"
}

Which results in something like:

    SCORE()    |    author     |       name        |  page_count   | release_date
---------------+---------------+-------------------+---------------+------------------------
2.288635       |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00.000Z
1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00.000Z
1.6086555      |Frank Herbert  |Children of Dune   |408            |1976-04-21T00:00:00.000Z
1.4005898      |Frank Herbert  |God Emperor of Dune|454            |1981-05-28T00:00:00.000Z

Note that you can return SCORE() by adding it to the where clause. This is possible even if you are not sorting by SCORE():

POST /_xpack/sql?format=txt
{
    "query": "SELECT SCORE(), * FROM library WHERE match(name, 'dune') ORDER BY page_count DESC"
}
    SCORE()    |    author     |       name        |  page_count   | release_date
---------------+---------------+-------------------+---------------+------------------------
2.288635       |Frank Herbert  |Dune               |604            |1965-06-01T00:00:00.000Z
1.4005898      |Frank Herbert  |God Emperor of Dune|454            |1981-05-28T00:00:00.000Z
1.6086555      |Frank Herbert  |Children of Dune   |408            |1976-04-21T00:00:00.000Z
1.8893257      |Frank Herbert  |Dune Messiah       |331            |1969-10-15T00:00:00.000Z

EXTRACTedit

Elasticsearch supports EXTRACT to extract fields from datetimes. You can run any datetime function with EXTRACT(<datetime_function> FROM <expression>). So

SELECT EXTRACT(DAY_OF_YEAR FROM CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50

is the equivalent to

SELECT DAY_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50