The OFFSET
clause excludes first N points from the query results. The SOFFSET
clause excludes first N series from the query results.
OFFSET clause
The OFFSET <N>
clause excludes the first N
points from the query results.
Syntax
SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] LIMIT_clause OFFSET <N> [SLIMIT_clause]
Description
N
indicates that the first N
points are excluded from the query results. If you need to use the LIMIT
clause in your query, you must include the LIMIT clause in your query and place the LIMIT clause before the OFFSET
clause. If a query has the OFFSET
clause but does not have the LIMIT
clause, inconsistent query results may be returned.
Notes: If the
WHERE
clause includes a time range, TSDB for InfluxDB® returns no results. If theOFFSET
clause is used, TSDB for InfluxDB® may return the points whose timestamps fall out of the time range.
Examples
Specify first N points to be excluded from the query results
> SELECT "water_level","location" FROM "h2o_feet" LIMIT 3 OFFSET 3
name: h2o_feet
time water_level location
---- ----------- --------
2015-08-18T00:06:00Z 2.116 santa_monica
2015-08-18T00:12:00Z 7.887 coyote_creek
2015-08-18T00:12:00Z 2.028 santa_monica
The query returns the fourth, fifth, and sixth points from the h2o_feet
measurement. If the preceding statement does not include the OFFSET 3
clause, the query returns the first, second, and third points from the measurement.
Specify first N points to be excluded from the query results, and include multiple clauses
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) ORDER BY time DESC LIMIT 2 OFFSET 2 SLIMIT 1
name: h2o_feet
tags: location=coyote_creek
time mean
---- ----
2015-08-18T00:12:00Z 7.8245
2015-08-18T00:00:00Z 8.0625
The query in this example includes multiple clauses and is complex. The following section explains each clause that is included in the query.
- The
SELECT
clause includes an InfluxQL function. - The
FROM
clause specifies a measurement. - The
WHERE
clause specifies the time range. - The
GROUP BY
clause groups query results based on all the tags and a 12-minute interval. The asterisk (*
) represents all the tags. - The
ORDER BY time DESC
clause returns query results based on timestamps in descending order. - The
LIMIT 2
clause limits the number of returned points to two. - The
OFFSET 2
clause excludes the first two average values from the query results. - The
SLIMIT 1
clause limits the number of series returned to one.
If the preceding statement does not include the OFFSET 2
clause, the query returns the first two average values of the query results.
name: h2o_feet
tags: location=coyote_creek
time mean
---- ----
2015-08-18T00:36:00Z 7.303
2015-08-18T00:24:00Z 7.5675
SOFFSET clause
The SOFFSET<N>
clause excludes the first N
series from the query results.
Syntax
SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] GROUP BY *[,time(time_interval)] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] SLIMIT_clause SOFFSET <N>
Description
N
indicates that the first N
series are excluded from the query results. If you need to use the SLIMIT
clause in your query, you must include the SLIMIT clause in your query and place the SLIMIT clause before the SOFFSET
clause. If a query has the SOFFSET
clause but does not have the SLIMIT
clause, inconsistent query results may be returned.
Notes: If
N
is greater than the total number of series, TSDB for InfluxDB® returns no results.
Examples
Specify first N series to be excluded from the query results
> SELECT "water_level" FROM "h2o_feet" GROUP BY * SLIMIT 1 SOFFSET 1
name: h2o_feet
tags: location=santa_monica
time water_level
---- -----------
2015-08-18T00:00:00Z 2.064
2015-08-18T00:06:00Z 2.116
[...]
2015-09-18T21:36:00Z 5.066
2015-09-18T21:42:00Z 4.938
The query returns the data for the series that are associated with the h2o_feet
measurement and the location = santa_monica
tag. If the preceding statement does not include the SOFFSET 1
clause, the query returns the data for the series associated with the h2o_feet
measurement and the location = coyote_creek
tag.
Specify first N series to be excluded from the query results, and include multiple clauses
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) ORDER BY time DESC LIMIT 2 OFFSET 2 SLIMIT 1 SOFFSET 1
name: h2o_feet
tags: location=santa_monica
time mean
---- ----
2015-08-18T00:12:00Z 2.077
2015-08-18T00:00:00Z 2.09
The query in this example includes multiple clauses and is complex. The following section explains each clause that is included in the query.
- The
SELECT
clause includes an InfluxQL function. - The
FROM
clause specifies a measurement. - The
WHERE
clause specifies the time range. - The
GROUP BY
clause groups query results based on all the tags and a 12-minute interval. The asterisk (*
) represents all the tags. - The
ORDER BY time DESC
clause returns query results based on timestamps in descending order. - The
LIMIT 2
clause limits the number of returned points to two. - The
OFFSET 2
clause excludes the first two average values from the query results. - The
SLIMIT 1
clause limits the number of series returned to one. - The
SOFFSET 1
clause excludes the first one series from the query results.
If the preceding statement does not include the SOFFSET 1
clause, the query returns the result of the other series.
name: h2o_feet
tags: location=coyote_creek
time mean
---- ----
2015-08-18T00:12:00Z 7.8245
2015-08-18T00:00:00Z 8.0625
InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.