PDFs

ADQL Reference:

Your Rating:
Results:
PatheticBadOKGoodOutstanding!
44 rates

Overview

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. If ORDER BY is not specified in the query, the results sort in descending timestamp order. The Analytics Search UI caps the number of search results at 10000 records.

Non-Aggregation Queries

Specify LIMIT N for non-aggregation queries to return the first N documents. 

SELECT * FROM transactions LIMIT 10

For non-aggregation queries, using the Analytics Query API, can return a maximum of 10,000 results. If a limit value higher than 10,000 is specified, the value is overridden with the maximum value.

Aggregation Queries

For aggregation queries a list of values is allowed in the LIMIT clause. 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 field1series(field2, '1m'), field3count(*) …

Bucketing functions consume a value from the LIMIT clause list of values, but that value is ignored because it doesn't apply to such functions. Because the default limit is 10, if there are more bucketing functions than user specified limits, the LIMIT clause will use 10 buckets. For example, if there 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 1000 for the first group by term or bucketing function. Subsequent terms are limited to 100 buckets. A larger value is overridden and replaced with the maximums. Aggregation queries can return a maximum of 10000 results.

REST API usage for limits is slightly different. Refer to the section "Querying Events" in Analytics Events API.


Aggregation Examples

QueryResult
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 application, transactionName, avg(responseTime)
FROM transactions LIMIT 500, 50

Returns the top 500 applications by count.
Within each of those buckets, the top 50 transactionNames by count.

SELECT application, transactionName, avg(responseTime)
FROM transactions LIMIT 2000, 500

Because of the enforced limits, this query returns the top 1000 applications by count.
Within each of those buckets, the top 100 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 doesn't apply to the functions, only to the group by terms, therefore in this query the limit value has no effect and the query returns all the data in the series function.

  • No labels