LIMIT Clause

ADQL Reference:

The LIMIT clause enforces a limit on the returned search results. LIMIT works differently for non-aggregation and aggregation queries.

The syntax is as follows:

[LIMIT integer [, integer]...]

An integer is a sequence of digits. The LIMIT clause has maximum of 20,000 results.

Specify LIMIT N for non-aggregation queries to return the first N documents. If ORDER BY is not specified in the query, the results sort in descending timestamp order. If a limit value higher than 20,000 is specified, the value is overridden with the maximum value.

Non-aggregation Example

SELECT * FROM transactions LIMIT 10

For aggregation queries a list of values is allowed. Each value applies to one "group by" term or bucketing function in the query. A "group by" term is a field name in an aggregation query. In the following example, field1 and field3 are group by terms, and series(field2, '1m') is a bucketing function:

SELECT field1, series(field2, '1m'), field3, count(*) …

Bucketing functions consume a value from the list, but that value is ignored because it doesn't apply to such functions. If there are more bucketing functions than user specified limits, the LIMIT clause uses 10 buckets. For example, if there are two limit values specified, but three functions or terms that could consume the limit values, the last one defaults to a limit of 10. The maximum number of buckets is 100. A larger value is overridden and replaced with the maximum of 100.

REST API Usage: If LIMIT is not specified in the SELECT statement, the value specified in the URL query parameter is used. If limit query parameter is also absent the default is 100.

Aggregation Examples

Query	Result
SELECT transactionName, avg(responseTime) FROM transactions	No limit specified, shows a maximum of top 10 results by count.
SELECT DISTINCT requestGUID FROM transactions	No limit specified, shows the top 10 results by count.
SELECT transactionName, avg(responseTime) FROM transactions LIMIT 50	Returns the top 50 results by count.
SELECT transactionName, avg(responseTime) FROM transactions LIMIT 200	200 is over the limit of 100, so this query returns the top 100 by count .
SELECT application, transactionName, avg(responseTime) FROM transactions LIMIT 20, 50	Returns the top 20 applications by count. Within each of those buckets, the top 50 transactionNames by count.
SELECT application, transactionName, avg(responseTime) FROM transactions LIMIT 20	Returns the top 20 applications by count. within each of those buckets the top 10 (using the default) transactionNames by count.
SELECT application, series(eventTimestamp, '1m'), transactionName, avg(responseTime) FROM transactions LIMIT 20, 25, 30	Returns the top 20 applications by count. Within those a date-time series with all the data. Within each date-time series bucket, shows the top 30 transactionNames by count. The value 25 is applied to the series function, but because series is a bucketing function, it does not actually use the limit.
SELECT application, series(eventTimestamp, '1m'), transactionName, avg(responseTime) FROM transactions LIMIT 20, 25	Returns the top 20 applications by count. Within those, shows a date-time series with all the data. Within each date-time series bucket the top 10 (using the default) transactionNames by count. The value 25 is applied to the series function, but because series is a bucketing function, it does not actually use the limit.
SELECT series(eventTimestamp, '1m'), avg(responseTime) FROM transactions LIMIT 20	Limit has no effect, so this query returns all the data in the series function