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(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
Return the smallest three field values in the water_level field key and in the
h2o_feetmeasurement.
SELECTBOTTOM("water_level",3)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
bottom
2019-08-29T14:30:00Z
-0.610
2019-08-29T14:36:00Z
-0.591
2019-08-30T15:18:00Z
-0.594
Select the bottom field value associated with a field key for two tags
Return the smallest field values in the water_level field key for two tag
values associated with the location tag key.
Select the bottom four field values associated with a field key and the relevant tags and fields
Return the smallest four field values in the water_level field key and the
relevant values of the location tag key and the level description field key.
Select the bottom three field values associated with a field key and include several clauses
Return the smallest three values in the water_level field key for each 24-minute
interval
between 2019-08-18T00:00:00Z and 2019-08-18T00:54:00Z with results in
descending timestamp order.
Notice that the GROUP BY time() clause
does not override the points’ original timestamps.
See Issue 1 in the section below for a
more detailed explanation of that behavior.
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.
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.
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.
Select the first field value associated with a field key
Return the oldest field value (determined by timestamp) associated with the
level description field key and in the h2o_feet measurement.
SELECTFIRST("level description")FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
first
2019-08-17T00:00:00Z
between 6 and 9 feet
Select the first field value associated with each field key in a measurement
Return the oldest field value (determined by timestamp) for each field key in the h2o_feet measurement.
The h2o_feet measurement has two field keys: level description and water_level.
SELECTFIRST(*)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
first_level description
first_water_level
1970-01-01T00:00:00Z
between 6 and 9 feet
8.120
Select the first field value associated with each field key that matches a regular expression
Return the oldest field value for each field key that includes the word level in the h2o_feet measurement.
SELECTFIRST(/level/)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
first_level description
first_water_level
1970-01-01T00:00:00Z
between 6 and 9 feet
8.120
Select the first value associated with a field key and the relevant tags and fields
Return the oldest field value (determined by timestamp) in the level description
field key and the relevant values of the location tag key and the water_level field key.
Select the first field value associated with a field key and include several clauses
Returns the oldest field value (determined by timestamp) in the water_level
field key in the time range
between 2019-08-17T23:48:00Z and 2019-08-18T00:54:00Z and
groups results into
12-minute time intervals and per tag.
Then fill
empty time intervals with 9.01, and it limit
the number of points and series returned to four and one.
Notice that the GROUP BY time() clause overrides the points’ original timestamps.
The timestamps in the results indicate the the start of each 12-minute time interval;
the first point in the results covers the time interval between 2019-08-17T23:48:00Z and just before 2019-08-18T00:00:00Z and the last point in the results covers the time interval between 2019-08-18T00:24:00Z and just before 2019-08-18T00:36:00Z.
LAST()
Returns the field value with the most recent timestamp.
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.
Select the last field values associated with a field key
Return the newest field value (determined by timestamp) associated with the
level description field key and in the h2o_feet measurement.
SELECTLAST("level description")FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
last
2019-09-17T21:42:00Z
between 3 and 6 feet
Select the last field values associated with each field key in a measurement
Return the newest field value (determined by timestamp) for each field key in the h2o_feet measurement.
The h2o_feet measurement has two field keys: level description and water_level.
SELECTLAST(*)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
last_level description
last_water_level
1970-01-01T00:00:00Z
between 3 and 6 feet
4.938
Select the last field value associated with each field key that matches a regular expression
Return the newest field value for each field key that includes the word level
in the h2o_feet measurement.
SELECTLAST(/level/)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
last_level description
last_water_level
1970-01-01T00:00:00Z
between 3 and 6 feet
4.938
Select the last field value associated with a field key and the relevant tags and fields
Return the newest field value (determined by timestamp) in the level description
field key and the relevant values of the location tag key and the water_level field key.
Select the last field value associated with a field key and include several clauses
Return the newest field value (determined by timestamp) in the water_level
field key in the time range
between 2019-08-17T23:48:00Z and 2019-08-18T00:54:00Z and
groups results into
12-minute time intervals and per tag.
Then fill
empty time intervals with 9.01, and it limit
the number of points and series returned to four and one.
Notice that the GROUP BY time() clause overrides the points’ original timestamps.
The timestamps in the results indicate the the start of each 12-minute time interval;
the first point in the results covers the time interval between 2019-08-17T23:48:00Z and just before 2019-08-18T00:00:00Z and the last point in the results covers the time interval between 2019-08-18T00:24:00Z and just before 2019-08-18T00:36:00Z.
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
Return the greatest field value in the water_level field key and in the h2o_feet measurement.
SELECTMAX("water_level")FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
max
2019-08-28T07:24:00Z
9.964
Select the maximum field value associated with each field key in a measurement
Return the greatest field value for each field key that stores numeric values
in the h2o_feet measurement.
The h2o_feet measurement has one numeric field: water_level.
SELECTMAX(*)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
max_water_level
2019-08-28T07:24:00Z
9.964
Select the maximum field value associated with each field key that matches a regular expression
Return the greatest field value for each field key that stores numeric values
and includes the word water in the h2o_feet measurement.
SELECTMAX(/level/)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
max_water_level
2019-08-28T07:24:00Z
9.964
Select the maximum field value associated with a field key and the relevant tags and fields
Return the greatest field value in the water_level field key and the relevant
values of the location tag key and the level description field key.
Select the maximum field value associated with a field key and include several clauses
Return the greatest field value in the water_level field key in the
time range
between 2019-08-17T23:48:00Z and 2019-08-18T00:54:00Z and
groups results into
12-minute time intervals and per tag.
Then fill
empty time intervals with 9.01, and it limit
the number of points and series returned to four and one.
Notice that the GROUP BY time() clause overrides the points’ original timestamps.
The timestamps in the results indicate the the start of each 12-minute time interval;
the first point in the results covers the time interval between 2019-08-17T23:48:00Z and just before 2019-08-18T00:00:00Z and the last point in the results covers the time interval between 2019-08-18T00:24:00Z and just before 2019-08-18T00:36:00Z.
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
Return the lowest field value in the water_level field key and in the h2o_feet measurement.
SELECTMIN("water_level")FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
min
2019-08-28T14:30:00Z
-0.610
Select the minimum field value associated with each field key in a measurement
Return the lowest field value for each field key that stores numeric values in the h2o_feet measurement.
The h2o_feet measurement has one numeric field: water_level.
SELECTMIN(*)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
min_water_level
2019-08-28T14:30:00Z
-0.610
Select the minimum field value associated with each field key that matches a regular expression
Return the lowest field value for each numeric field with water in the field
key in the h2o_feet measurement.
SELECTMIN(/level/)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
min_water_level
2019-08-28T14:30:00Z
-0.610
Select the minimum field value associated with a field key and the relevant tags and fields
Return the lowest field value in the water_level field key and the relevant
values of the location tag key and the level description field key.
Select the minimum field value associated with a field key and include several clauses
Return the lowest field value in the water_level field key in the
time range
between 2019-08-17T23:48:00Z and 2019-08-18T00:54:00Z and
groups results into
12-minute time intervals and per tag.
Then fill
empty time intervals with 9.01, and it limit
the number of points and series returned to four and one.
Notice that the GROUP BY time() clause overrides the points’ original timestamps.
The timestamps in the results indicate the the start of each 12-minute time interval;
the first point in the results covers the time interval between 2019-08-17T23:48:00Z and just before 2019-08-18T00:00:00Z and the last point in the results covers the time interval between 2019-08-18T00:24:00Z and just before 2019-08-18T00:36:00Z.
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
Return the field value that is larger than five percent of the field values in
the water_level field key and in the h2o_feet measurement.
SELECTPERCENTILE("water_level",5)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
percentile
2019-09-01T17:54:00Z
1.122
Select the fifth percentile field value associated with each field key in a measurement
Return the field value that is larger than five percent of the field values in
each field key that stores numeric values in the h2o_feet measurement.
The h2o_feet measurement has one numeric field: water_level.
SELECTPERCENTILE(*,5)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
percentile_water_level
2019-09-01T17:54:00Z
1.122
Select fifth percentile field value associated with each field key that matches a regular expression
Return the field value that is larger than five percent of the field values in
each numeric field with water in the field key.
SELECTPERCENTILE(/level/,5)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
percentile_water_level
2019-09-01T17:54:00Z
1.122
Select the fifth percentile field values associated with a field key and the relevant tags and fields
Return the field value that is larger than five percent of the field values in
the water_level field key and the relevant values of the location tag key
and the level description field key.
Select the twentieth percentile field value associated with a field key and include several clauses
Return the field value that is larger than 20 percent of the values in the
water_level field in the time range
between 2019-08-17T23:48:00Z and 2019-08-18T00:54:00Z and group results into 24-minute intervals.
Then fill
empty time intervals with 15 and limit
the number of points returned to two.
Notice that the GROUP BY time() clause overrides the points’ original timestamps.
The timestamps in the results indicate the the start of each 24-minute time interval; the first point in the results covers the time interval between 2019-08-17T23:36:00Z and just before 2019-08-18T00:00:00Z and the last point in the results covers the time interval between 2019-08-18T00:00:00Z and just before 2019-08-18T00:24:00Z.
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(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
Return two randomly selected points from the water_level field key and in the h2o_feet measurement.
SELECTSAMPLE("water_level",2)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
sample
2019-08-22T03:42:00Z
7.218
2019-08-28T20:18:00Z
2.848
Select a sample of the field values associated with each field key in a measurement
Return two randomly selected points for each field key in the h2o_feet measurement.
The h2o_feet measurement has two field keys: level description and water_level.
SELECTSAMPLE(*,2)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
sample_level description
sample_water_level
2019-08-23T17:30:00Z
below 3 feet
2019-09-08T19:18:00Z
8.379
2019-09-09T03:54:00Z
between 6 and 9 feet
2019-09-16T04:48:00Z
1.437
Select a sample of the field values associated with each field key that matches a regular expression
Return two randomly selected points for each field key that includes the word
level in the h2o_feet measurement.
SELECTSAMPLE(/level/,2)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
sample_level description
sample_water_level
2019-08-19T20:24:00Z
4.951
2019-08-26T06:30:00Z
below 3 feet
2019-09-10T09:06:00Z
1.312
2019-09-16T21:00:00Z
between 3 and 6 feet
Select a sample of the field values associated with a field key and the relevant tags and fields
Return two randomly selected points from the water_level field key and the
relevant values of the location tag and the level description field.
Select a sample of the field values associated with a field key and include several clauses
Return one randomly selected point from the water_level field key in the
time range
between 2019-08-18T00:00:00Z and 2019-08-18T00:30:00Z and
group
results into 18-minute intervals.
Notice that the GROUP BY time() clause does not override the points’ original timestamps.
See Issue 1 in the section below for a
more detailed explanation of that behavior.
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.
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(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
Return the greatest three field values in the water_level field key and in the
h2o_feetmeasurement.
SELECTTOP("water_level",3)FROM"h2o_feet"
Copy
Fill window
name: h2o_feet
time
top
2019-08-28T07:18:00Z
9.957
2019-08-28T07:24:00Z
9.964
2019-08-28T07:30:00Z
9.954
Select the top field value associated with a field key for two tags
Return the greatest field values in the water_level field key for two tag
values associated with the location tag key.
Select the top four field values associated with a field key and the relevant tags and fields
Return the greatest four field values in the water_level field key and the
relevant values of the location tag key and the level description field key.
Select the top three field values associated with a field key and include several clauses
Return the greatest three values in the water_level field key for each 24-minute
interval
between 2019-08-18T00:00:00Z and 2019-08-18T00:54:00Z with results in
descending timestamp order.
Notice that the GROUP BY time() clause does not override the points’ original timestamps.
See Issue 1 in the section below for a more detailed explanation of that behavior.
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.
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.
Thank you for being part of our community!
We welcome and encourage your feedback and bug reports for InfluxDB and this documentation.
To find support, use the following resources:
InfluxDB v3 enhancements and InfluxDB Clustered is now generally available
New capabilities, including faster query performance and management
tooling advance the InfluxDB v3 product line.
InfluxDB Clustered is now generally available.
InfluxDB v3 performance and features
The InfluxDB v3 product line has seen significant enhancements in query
performance and has made new management tooling available. These enhancements
include an operational dashboard to monitor the health of your InfluxDB cluster,
single sign-on (SSO) support in InfluxDB Cloud Dedicated, and new management
APIs for tokens and databases.
You are currently viewing documentation specific to InfluxDB Cloud
powered by the TSM storage engine, which
offers different functionality than InfluxDB Cloud
Serverless
powered by the v3 storage engine.