Overview

The ORDER BY clause enables you to sort the query results by one or more fields. The syntax is:

[ORDER BY {field_name | alias} [ASC | DESC] [, {field_name | `alias`} [ASC | DESC]]...]

Rules governing ordering include:

  • Sort order, ascending (ASC) or descending (DESC) is applied per field. If not specified, the ascending (ASC) order is the default.
  • ORDER BY is supported on fields or aliases specified in the SELECT clause.
  • Aliases used in the ORDER BY clause should be surrounded with back quotes if they contain spaces or other special characters.
  • ORDER BY works on single-value metric functions and single-value math expressions. ORDER BY can not be used with percentile or stats.
  • ORDER BY on nested fields or metric aggregations that operate on nested fields is not supported. A nested field is any field that has a "." in the name, such as segments.errorList.errorType or btdata.estimatedtime.

Ordering functionality for metric and bucketing functions includes:

  • ORDER BY can be used to order the buckets on a bucketed field in a bucketing function. For example, see the "Series aggregations - Ordering based on the keys of the series buckets." in the table below.

  • ORDER BY can be used on metric functions that follow bucketing functions, including filtered metric functions. Ordering by the metric value orders the resulting buckets. The metric function must be aliased and referenced by its alias such as:

    SELECT transactionName, avg(responseTime) AS averageResponseTime FROM transactions ORDER BY averageResponseTime
  • Compound orderings can be specified for functions that provide implicit GROUP BY functionality.
  • A single ordering can be defined for the series function, no ordering is allowed for the range function.
  • ORDER BY can accept math expressions on fields, but not math expressions on metric functions. If the value of a math expression is null it is ordered at the end of the results in ascending order. The following is an invalid query:
    SELECT transactionName, max(discountValue) / max(cartTotal) AS ratio FROM transactions ORDER BY ratio

Examples

For this resultQuery
An ordering of queries with no aggregationsSELECT field_name  FROM event_type ORDER BY field_name ASC
Group By - Ordering by the lexicographic ordering of the terms. The fields included in the ORDER BY clause must be in the same order as in the query itself.SELECT field_name, count(*)  FROM event_type ORDER BY field_name DESC
Returns a sorted set of timestamp ranges

SELECT series(eventTimestamp, 10m), count(*) FROM transactions ORDER BY eventTimestamp DESC

Series aggregations - Ordering based on the keys of the series buckets.SELECT series(salary, 20000), count(salary) FROM event_type ORDER BY salary DESC
Date series aggregations - Ordering based on the date value of the keys of the series buckets

SELECT series(eventTimestamp, '1h'), count(eventTimestamp) FROM transactions ORDER BY eventTimestamp

Order by an alias.

SELECT responseTime AS RT FROM transactions ORDER BY RT

Order by an aliased math expression.

SELECT discountValue / cartTotal AS ratio FROM transactions ORDER BY ratio