Documentation

InfluxDB OSS

v2

InfluxQL selector functions

Use selector functions to assess, select, and return values in your data. Selector functions return one or more rows with the selected values from each InfluxQL group.

Each selector function below covers syntax, including parameters to pass to the function, and examples of how to use the function. Examples use NOAA water sample data.

BOTTOM()

Returns the smallest N field values. BOTTOM() supports int64 and float64 field value data types.

Note: BOTTOM() returns the field value with the earliest timestamp if there’s a tie between two or more values for the smallest value.

Syntax

SELECT BOTTOM(<field_key>[,<tag_key(s)>],<N> )[,<tag_key(s)>|<field_key(s)>] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

BOTTOM(field_key,N)
Returns the smallest N field values associated with the field key.

BOTTOM(field_key,tag_key,N)
Returns the smallest field value for N tag values of the tag key. Add a comma between multiple tag keys: tag_key,tag_key.

BOTTOM((field_key,N),tag_key,field_key)
Returns the smallest N field values associated with the field key in the parentheses and the relevant tag and/or field. Add a comma between multiple tag or field keys: tag_key,tag_key,field_key,field_key.

Examples

Select the bottom three field values associated with a field key

Select the bottom field value associated with a field key for two tags

Select the bottom four field values associated with a field key and the relevant tags and fields

Select the bottom three field values associated with a field key and include several clauses

Common issues with BOTTOM()

BOTTOM() with a GROUP BY time() clause

Queries with BOTTOM() and a GROUP BY time() clause return the specified number of points per GROUP BY time() interval. For most GROUP BY time() queries, the returned timestamps mark the start of the GROUP BY time() interval. GROUP BY time() queries with the BOTTOM() function behave differently; they maintain the timestamp of the original data point.

Example

The query below returns two points per 18-minute GROUP BY time() interval. Notice that the returned timestamps are the points’ original timestamps; they are not forced to match the start of the GROUP BY time() intervals.

SELECT BOTTOM("water_level",2) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(18m)

name: h2o_feet

timebottom
2019-08-18T00:00:00Z2.064
2019-08-18T00:12:00Z2.028
2019-08-18T00:24:00Z2.041
2019-08-18T00:30:00Z2.051

Notice that the first two rows contain the smallest values from the first time interval and the last two rows contains the smallest values for the second time interval.

BOTTOM() and a tag key with fewer than N tag values

Queries with the syntax SELECT BOTTOM(<field_key>,<tag_key>,<N>) can return fewer points than expected. If the tag key has X tag values, the query specifies N values, and X is smaller than N, then the query returns X points.

Example

The query below asks for the smallest field values of water_level for three tag values of the location tag key. Because the location tag key has two tag values (santa_monica and coyote_creek), the query returns two points instead of three.

SELECT BOTTOM("water_level","location",3) FROM "h2o_feet"

name: h2o_feet

timebottomlocation
2019-08-29T10:36:00Z-0.243santa_monica
2019-08-29T14:30:00Z-0.610coyote_creek

FIRST()

Returns the field value with the oldest timestamp.

Syntax

SELECT FIRST(<field_key>)[,<tag_key(s)>|<field_key(s)>] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

FIRST(field_key)
Returns the oldest field value (determined by timestamp) associated with the field key.

FIRST(/regular_expression/)
Returns the oldest field value (determined by timestamp) associated with each field key that matches the regular expression.

FIRST(*)
Returns the oldest field value (determined by timestamp) associated with each field key in the measurement.

FIRST(field_key),tag_key(s),field_key(s)
Returns the oldest field value (determined by timestamp) associated with the field key in the parentheses and the relevant tag and/or field.

FIRST() supports all field value data types.

Examples

Select the first field value associated with a field key

Select the first field value associated with each field key in a measurement

Select the first field value associated with each field key that matches a regular expression

Select the first value associated with a field key and the relevant tags and fields

Select the first field value associated with a field key and include several clauses

LAST()

Returns the field value with the most recent timestamp.

Syntax

SELECT LAST(<field_key>)[,<tag_key(s)>|<field_keys(s)>] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

LAST(field_key)
Returns the newest field value (determined by timestamp) associated with the field key.

LAST(/regular_expression/)
Returns the newest field value (determined by timestamp) associated with each field key that matches the regular expression.

LAST(*)
Returns the newest field value (determined by timestamp) associated with each field key in the measurement.

LAST(field_key),tag_key(s),field_key(s)
Returns the newest field value (determined by timestamp) associated with the field key in the parentheses and the relevant tag and/or field.

LAST() supports all field value data types.

Examples

Select the last field values associated with a field key

Select the last field values associated with each field key in a measurement

Select the last field value associated with each field key that matches a regular expression

Select the last field value associated with a field key and the relevant tags and fields

Select the last field value associated with a field key and include several clauses

MAX()

Returns the greatest field value.

Syntax

SELECT MAX(<field_key>)[,<tag_key(s)>|<field__key(s)>] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

MAX(field_key)
Returns the greatest field value associated with the field key.

MAX(/regular_expression/)
Returns the greatest field value associated with each field key that matches the regular expression.

MAX(*)
Returns the greatest field value associated with each field key in the measurement.

MAX(field_key),tag_key(s),field_key(s)
Returns the greatest field value associated with the field key in the parentheses and the relevant tag and/or field.

MAX() supports int64 and float64 field value data types.

Examples

Select the maximum field value associated with a field key

Select the maximum field value associated with each field key in a measurement

Select the maximum field value associated with each field key that matches a regular expression

Select the maximum field value associated with a field key and the relevant tags and fields

Select the maximum field value associated with a field key and include several clauses

MIN()

Returns the lowest field value.

Syntax

SELECT MIN(<field_key>)[,<tag_key(s)>|<field_key(s)>] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

MIN(field_key)
Returns the lowest field value associated with the field key.

MIN(/regular_expression/)
Returns the lowest field value associated with each field key that matches the regular expression.

MIN(*)
Returns the lowest field value associated with each field key in the measurement.

MIN(field_key),tag_key(s),field_key(s)
Returns the lowest field value associated with the field key in the parentheses and the relevant tag and/or field.

MIN() supports int64 and float64 field value data types.

Examples

Select the minimum field value associated with a field key

Select the minimum field value associated with each field key in a measurement

Select the minimum field value associated with each field key that matches a regular expression

Select the minimum field value associated with a field key and the relevant tags and fields

Select the minimum field value associated with a field key and include several clauses

PERCENTILE()

Returns the Nth percentile field value.

Syntax

SELECT PERCENTILE(<field_key>, <N>)[,<tag_key(s)>|<field_key(s)>] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

PERCENTILE(field_key,N)
Returns the Nth percentile field value associated with the field key.

PERCENTILE(/regular_expression/,N)
Returns the Nth percentile field value associated with each field key that matches the regular expression.

PERCENTILE(*,N)
Returns the Nth percentile field value associated with each field key in the measurement.

PERCENTILE(field_key,N),tag_key(s),field_key(s)
Returns the Nth percentile field value associated with the field key in the parentheses and the relevant tag and/or field.

N must be an integer or floating point number between 0 and 100, inclusive. PERCENTILE() supports int64 and float64 field value data types.

Examples

Select the fifth percentile field value associated with a field key

Select the fifth percentile field value associated with each field key in a measurement

Select fifth percentile field value associated with each field key that matches a regular expression

Select the fifth percentile field values associated with a field key and the relevant tags and fields

Select the twentieth percentile field value associated with a field key and include several clauses

Common issues with PERCENTILE()

PERCENTILE() compared to other InfluxQL functions

  • PERCENTILE(<field_key>,100) is equivalent to MAX(<field_key>).
  • PERCENTILE(<field_key>, 50) is nearly equivalent to MEDIAN(<field_key>), except the MEDIAN() function returns the average of the two middle values if the field key contains an even number of field values.
  • PERCENTILE(<field_key>,0) is not equivalent to MIN(<field_key>). This is a known issue.

SAMPLE()

Returns a random sample of N field values. SAMPLE() uses reservoir sampling to generate the random points.

Syntax

SELECT SAMPLE(<field_key>, <N>)[,<tag_key(s)>|<field_key(s)>] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

SAMPLE(field_key,N)
Returns N randomly selected field values associated with the field key.

SAMPLE(/regular_expression/,N)
Returns N randomly selected field values associated with each field key that matches the regular expression.

SAMPLE(*,N)
Returns N randomly selected field values associated with each field key in the measurement.

SAMPLE(field_key,N),tag_key(s),field_key(s)
Returns N randomly selected field values associated with the field key in the parentheses and the relevant tag and/or field.

N must be an integer. SAMPLE() supports all field value data types.

Examples

Select a sample of the field values associated with a field key

Select a sample of the field values associated with each field key in a measurement

Select a sample of the field values associated with each field key that matches a regular expression

Select a sample of the field values associated with a field key and the relevant tags and fields

Select a sample of the field values associated with a field key and include several clauses

Common issues with SAMPLE()

SAMPLE() with a GROUP BY time() clause

Queries with SAMPLE() and a GROUP BY time() clause return the specified number of points (N) per GROUP BY time() interval. For most GROUP BY time() queries, the returned timestamps mark the start of the GROUP BY time() interval. GROUP BY time() queries with the SAMPLE() function behave differently; they maintain the timestamp of the original data point.

Example

The query below returns two randomly selected points per 18-minute GROUP BY time() interval. Notice that the returned timestamps are the points’ original timestamps; they are not forced to match the start of the GROUP BY time() intervals.

SELECT SAMPLE("water_level",2) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(18m)

name: h2o_feet

timesample
2019-08-18T00:06:00Z2.116
2019-08-18T00:12:00Z2.028
2019-08-18T00:18:00Z2.126
2019-08-18T00:30:00Z2.051

Notice that the first two rows are randomly-selected points from the first time interval and the last two rows are randomly-selected points from the second time interval.

TOP()

Returns the greatest N field values.

Syntax

SELECT TOP( <field_key>[,<tag_key(s)>],<N> )[,<tag_key(s)>|<field_key(s)>] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause]

TOP(field_key,N)
Returns the greatest N field values associated with the field key.

TOP(field_key,tag_key(s),N)
Returns the greatest field value for N tag values of the tag key.

TOP(field_key,N),tag_key(s),field_key(s)
Returns the greatest N field values associated with the field key in the parentheses and the relevant tag and/or field.

TOP() supports int64 and float64 field value data types.

Note: TOP() returns the field value with the earliest timestamp if there’s a tie between two or more values for the greatest value.

Examples

Select the top three field values associated with a field key

Select the top field value associated with a field key for two tags

Select the top four field values associated with a field key and the relevant tags and fields

Select the top three field values associated with a field key and include several clauses

Common issues with TOP()

TOP() with a GROUP BY time() clause

Queries with TOP() and a GROUP BY time() clause return the specified number of points per GROUP BY time() interval. For most GROUP BY time() queries, the returned timestamps mark the start of the GROUP BY time() interval. GROUP BY time() queries with the TOP() function behave differently; they maintain the timestamp of the original data point.

Example

The query below returns two points per 18-minute GROUP BY time() interval. Notice that the returned timestamps are the points’ original timestamps; they are not forced to match the start of the GROUP BY time() intervals.

SELECT TOP("water_level",2) FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:30:00Z' AND "location" = 'santa_monica' GROUP BY time(18m)

name: h2o_feet

timetop
2019-08-18T00:00:00Z2.064
2019-08-18T00:06:00Z2.116
2019-08-18T00:18:00Z2.126
2019-08-18T00:30:00Z2.051

Notice that the first two rows are the greatest points for the first time interval and the last two rows are the greatest points for the second time interval.

TOP() and a tag key with fewer than N tag values

Queries with the syntax SELECT TOP(<field_key>,<tag_key>,<N>) can return fewer points than expected. If the tag key has X tag values, the query specifies N values, and X is smaller than N, then the query returns X points.

Example

The query below asks for the greatest field values of water_level for three tag values of the location tag key. Because the location tag key has two tag values (santa_monica and coyote_creek), the query returns two points instead of three.

SELECT TOP("water_level","location",3) FROM "h2o_feet"

name: h2o_feet

timetoplocation
2019-08-29T03:54:00Z7.205santa_monica
2019-08-29T07:24:00Z9.964coyote_creek

Was this page helpful?

Thank you for your feedback!

© 2024 InfluxData, Inc.

What is your InfluxDB OSS URL?

Customize your InfluxDB OSS URL and we’ll update code examples for you.

Default
Custom

For more information, see InfluxDB OSS URLs.

Thank you for your feedback!

Let us know what we can do better:

Thank you!

No thanks

The future of Flux

Flux is going into maintenance mode. You can continue using it as you currently are without any changes to your code.

Flux is going into maintenance mode and will not be supported in InfluxDB 3.0. This was a decision based on the broad demand for SQL and the continued growth and adoption of InfluxQL. We are continuing to support Flux for users in 1.x and 2.x so you can continue using it with no changes to your code. If you are interested in transitioning to InfluxDB 3.0 and want to future-proof your code, we suggest using InfluxQL.

For information about the future of Flux, see the following: