Sorting and limiting (groupBy)

Apache Druid supports two query languages: Druid SQL and native queries. This document describes the native language. For information about sorting in SQL, refer to the SQL documentation.

The limitSpec field provides the functionality to sort and limit the set of results from a groupBy query. If you group by a single dimension and are ordering by a single metric, we highly recommend using TopN Queries instead. The performance will be substantially better. Available options are:

DefaultLimitSpec

The default limit spec takes a limit and the list of columns to do an orderBy operation over. The grammar is:

{
    "type"    : "default",
    "limit"   : <optional integer>,
    "offset"  : <optional integer>,
    "columns" : [<optional list of OrderByColumnSpec>],
}

The “limit” parameter is the maximum number of rows to return.

The “offset” parameter tells Druid to skip this many rows when returning results. If both “limit” and “offset” are provided, then “offset” will be applied first, followed by “limit”. For example, a spec with limit 100 and offset 10 will return 100 rows starting from row number 10. Internally, the query is executed by extending the limit by the offset and then discarding a number of rows equal to the offset. This means that raising the offset will increase resource usage by an amount similar to increasing the limit.

Together, “limit” and “offset” can be used to implement pagination. However, note that if the underlying datasource is modified in between page fetches in ways that affect overall query results, then the different pages will not necessarily align with each other.

OrderByColumnSpec

OrderByColumnSpecs indicate how to do order by operations. Each order-by condition can be a jsonString or a map of the following form:

{
    "dimension" : "<Any dimension or metric name>",
    "direction" : <"ascending"|"descending">,
    "dimensionOrder" : <"lexicographic"(default)|"alphanumeric"|"strlen"|"numeric">
}

If only the dimension is provided (as a JSON string), the default order-by is ascending with lexicographic sorting.

See Sorting Orders for more information on the sorting orders specified by “dimensionOrder”.