Offset And Soffset

Use OFFSET and SOFFSET to paginate points and series returned.

• OFFSET clause
  • Syntax
  • Examples
• The SOFFSET clause
  • Syntax
  • Examples

OFFSET clause

OFFSET <N> paginates N points in the query results.

Syntax

SELECT_clause FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] LIMIT_clause OFFSET <N> [SLIMIT_clause]

N specifies the number of points to paginate. The OFFSET clause requires a LIMIT clause.

{{% note %}} Note: InfluxDB returns no results if the WHERE clause includes a time range and the OFFSET clause would cause InfluxDB to return points with timestamps outside of that time range. {{% /note %}}

Examples

{{< expand-wrapper >}}

{{% expand "Paginate points" %}}

SELECT "water_level","location" FROM "h2o_feet" LIMIT 3 OFFSET 3

Output: {{% influxql/table-meta %}} Name: h2o_feet {{% /influxql/table-meta %}}

The query returns the fourth, fifth, and sixth points from the h2o_feet measurement. If the query did not include OFFSET 3, it would return the first, second, and third points from that measurement.

{{% /expand %}}

{{% expand "Paginate points and include several clauses" %}}

SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:42:00Z' GROUP BY *,time(12m) ORDER BY time DESC LIMIT 2 OFFSET 2 SLIMIT 1

Output: {{% influxql/table-meta %}} name: h2o_feet
tags: location=coyote_creek {{% /influxql/table-meta %}}

In this example:

• The SELECT clause specifies the InfluxQL MEAN() function.
• The [FROM clause] (/influxdb/version/query-data/influxql/explore-data/select/#from-clause) specifies a single measurement.
• The WHERE clause specifies the time range for the query.
• The GROUP BY clause groups results by all tags (*) and into 12-minute intervals.
• The ORDER BY time DESC clause returns results in descending timestamp order.
• The LIMIT 2 clause limits the number of points returned to two.
• The OFFSET 2 clause excludes the first two averages from the query results.
• The SLIMIT 1 clause limits the number of series returned to one.

Without OFFSET 2, the query would return the first two averages of the query results:

Output: {{% influxql/table-meta %}} name: h2o_feet
tags: location=coyote_creek {{% /influxql/table-meta %}}

{{< /expand >}}

{{< /expand-wrapper >}}

SOFFSET clause

SOFFSET <N> paginates N series in the query results.

Syntax

SELECT_clause FROM_clause [WHERE_clause] GROUP BY *[,time(time_interval)] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] SLIMIT_clause SOFFSET <N>

N specifies the number of series to paginate. The SOFFSET clause requires an [SLIMIT clause](/influxdb/version/query-data/influxql/explore-data/limit-and-slimit/. Using the SOFFSET clause without an SLIMIT clause can cause inconsistent query results. SLIMIT queries must include GROUP BY *. {{% note %}} Note: InfluxDB returns no results if the SOFFSET clause paginates through more than the total number of series. {{% /note %}}

Examples

{{< expand-wrapper >}}

{{% expand "Paginate series" %}}

Paginate series

SELECT "water_level" FROM "h2o_feet" GROUP BY * SLIMIT 1 SOFFSET 1

Output: {{% influxql/table-meta %}} name: h2o_feet
tags: location=santa_monica {{% /influxql/table-meta %}}

The results above are partial, as the data set is quite large. The query returns data for the series associated with the h2o_feet measurement and the location = santa_monica tag. Without SOFFSET 1, the query returns data for the series associated with the h2o_feet measurement and the location = coyote_creek tag.

{{% /expand %}}

{{% expand "Paginate points and include several clauses" %}}

Paginate series and include all clauses

SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:42:00Z' GROUP BY *,time(12m) ORDER BY time DESC LIMIT 2 OFFSET 2 SLIMIT 1 SOFFSET 1

Output: {{% influxql/table-meta %}} name: h2o_feet
tags: location=santa_monica {{% /influxql/table-meta %}}

In this example:

• The SELECT clause specifies an InfluxQL function.
• The FROM clause specifies a single measurement.
• The WHERE clause specifies the time range for the query.
• The GROUP BY clause groups results by all tags (*) and into 12-minute intervals.
• The ORDER BY time DESC clause returns results in descending timestamp order.
• The LIMIT 2 clause limits the number of points returned to two.
• The OFFSET 2 clause excludes the first two averages from the query results.
• The SLIMIT 1 clause limits the number of series returned to one.
• The SOFFSET 1 clause paginates the series returned.

Without SOFFSET 1, the query would return the results for a different series:

Output: {{% influxql/table-meta %}} name: h2o_feet
tags: location=coyote_creek {{% /influxql/table-meta %}}

{{% /expand %}}

{{< /expand-wrapper >}}