ES|QL LIMIT command

The LIMIT processing command limits the number of rows returned.

Syntax

Stack 9.0+

LIMIT max_number_of_rows

Serverless Stack Preview 9.4-9.5 …

LIMIT max_number_of_rows BY grouping_expr1[, ..., grouping_exprN]

Parameters

max_number_of_rows: The maximum number of rows to return. When BY is specified, the maximum number of rows to return per group.
grouping_exprX Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Generally available This functionality is generally available and ready for production usage. Serverless interfaces and procedures might differ from classic Elastic Stack deployments. The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Planned We plan to add this functionality in a future Elastic Stack update. Subject to changes. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.: An expression that outputs the values to group by.

Description

Use the LIMIT processing command to limit the number of rows returned.

When BY is specified, up to max_number_of_rows rows are retained for each distinct combination of the grouping expressions. Precede LIMIT <N> BY with a SORT to keep the top N for each group.

For instance,

FROM index | WHERE field = "value"

is equivalent to:

FROM index | WHERE field = "value" | LIMIT 1000

Queries do not return more than 10,000 rows, regardless of the LIMIT command’s value. This is a configurable upper limit.

To overcome this limitation:

Reduce the result set size by modifying the query to only return relevant data. Use WHERE to select a smaller subset of the data.
Shift any post-query processing to the query itself. You can use the ES|QL STATS command to aggregate data in the query.

The upper limit only applies to the number of rows that are output by the query, not to the number of documents it processes: the query runs on the full data set.

Consider the following two queries:

FROM index | WHERE field0 == "value" | LIMIT 20000

and

FROM index | STATS AVG(field1) BY field2 | LIMIT 20000

In both cases, the filtering by field0 in the first query or the grouping by field2 in the second is applied over all the documents present in the index, irrespective of their number or indexes size. However, both queries will return at most 10,000 rows, even if there were more rows available to return.

The default and maximum limits can be changed using these dynamic cluster settings:

esql.query.result_truncation_default_size
esql.query.result_truncation_max_size

For time-series queries that use the TS or PROMQL source commands (including requests to the PromQL HTTP API), the default and maximum limits are higher and can be changed using these cluster settings:

esql.query.timeseries_result_truncation_default_size
esql.query.timeseries_result_truncation_max_size

Modifying these values involves trade-offs. A larger result-set involves a higher memory pressure and increased processing times. Internode traffic within and across clusters can also increase.

These limitations are similar to those enforced by the search API for pagination.

Functionality	Search	ES\|QL
Results returned by default	10	1,000
Results returned by default (time-series aggregations)	10	10,000
Default upper limit	10,000	10,000
Default upper limit (time-series aggregations)	10,000	10,000,000
Specify number of results	`size`	`LIMIT`
Change default number of results	n/a	esql.query.result_truncation_default_size
Change default number of results (time-series aggregations)	n/a	esql.query.timeseries_result_truncation_default_size
Change default upper limit	index-max-result-window	esql.query.result_truncation_max_size
Change default upper limit (time-series aggregations)	index-max-result-window	esql.query.timeseries_result_truncation_max_size

Examples

Limit

FROM employees
| SORT emp_no ASC
| LIMIT 5

With groups

FROM employees
| SORT salary DESC
| LIMIT 1 BY gender
| KEEP first_name, last_name, salary, gender

first_name:keyword	last_name:keyword	salary:integer	gender:keyword
Otmar	Herbst	74999	M
Tzvetan	Zielinski	74572	F
Lillian	Haddadi	73717	null

Group by multiple values

FROM employees
| SORT salary DESC
| LIMIT 1 BY languages, gender