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.
Examples use the sample data set provided in the
Get started with InfluxDB tutorial.
Missing InfluxQL functions
Some InfluxQL functions are in the process of being rearchitected to work with
the InfluxDB 3.0 storage engine. If a function you need is not here, check the
InfluxQL feature support page
for more information.
BOTTOM()
Returns the smallest N
field values.
BOTTOM()
supports int64 and float64 field value data types.
BOTTOM(field_expression[, tag_expression_1[, ..., tag_expression_n]], N)
Note: BOTTOM()
returns the field value with the earliest timestamp if
there’s a tie between two or more values for the smallest value.
Arguments
- field_expression: Expression to identify the field to operate on.
Can be a field key
or constant.
- tag_expression: Expression to identify a tag key to segment by.
Can be a tag key
or constant. Comma-delimit multiple tags.
- N: Number of results to return from each InfluxQL group or specified tag segment.
Notable behaviors
Examples
Select the bottom three values of a field
SELECT BOTTOM(temp, 3) FROM home
time |
bottom |
2022-01-01T08:00:00Z |
21 |
2022-01-01T08:00:00Z |
21.1 |
2022-01-01T09:00:00Z |
21.4 |
Select the bottom field value for two unique tag values
SELECT BOTTOM(temp, room, 2) FROM home
time |
bottom |
room |
2022-01-01T08:00:00Z |
21 |
Kitchen |
2022-01-01T08:00:00Z |
21.1 |
Living Room |
Select the bottom three field values and the tag value associated with each
SELECT BOTTOM(temp, 3), room FROM home
time |
bottom |
room |
2022-01-01T08:00:00Z |
21 |
Kitchen |
2022-01-01T08:00:00Z |
21.1 |
Living Room |
2022-01-01T09:00:00Z |
21.4 |
Living Room |
Select the bottom field values for unique tag values and within time windows (grouped by time)
SELECT
BOTTOM(temp, room, 2)
FROM home
WHERE
time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T12:00:00Z'
GROUP BY time(2h)
time |
bottom |
room |
2022-01-01T08:00:00Z |
21 |
Kitchen |
2022-01-01T08:00:00Z |
21.1 |
Living Room |
2022-01-01T10:00:00Z |
21.8 |
Living Room |
2022-01-01T11:00:00Z |
22.4 |
Kitchen |
2022-01-01T12:00:00Z |
22.2 |
Living Room |
2022-01-01T12:00:00Z |
22.5 |
Kitchen |
Notice that when grouping by time, BOTTOM()
maintains the point’s original timestamp.
FIRST()
Returns the field value with the oldest timestamp.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports all field data types.
Notable behaviors
Examples
Select the first value for a field
SELECT FIRST(temp) FROM home
time |
first |
2022-01-01T08:00:00Z |
21.1 |
Select the first value from each field
SELECT FIRST(*) FROM home
time |
first_co |
first_hum |
first_temp |
1970-01-01T00:00:00Z |
0 |
35.9 |
21.1 |
Select the first value from field keys that match a regular expression
SELECT FIRST(/^[th]/) FROM home
time |
first_hum |
first_temp |
1970-01-01T00:00:00Z |
35.9 |
21.1 |
Select the first value from a field within time windows (grouped by time)
SELECT
FIRST(temp)
FROM home
WHERE
room = 'Kitchen'
AND time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)
time |
first |
2022-01-01T06:00:00Z |
21 |
2022-01-01T12:00:00Z |
22.5 |
2022-01-01T18:00:00Z |
23.3 |
Notice that when grouping by time, FIRST()
overrides the point’s original timestamp.
LAST()
Returns the field value with the most recent timestamp.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports all field data types.
Notable behaviors
Examples
Select the last value for a field
SELECT LAST(temp) FROM home
time |
last |
2022-01-01T20:00:00Z |
22.7 |
Select the last value from each field
time |
last_co |
last_hum |
last_temp |
1970-01-01T00:00:00Z |
26 |
36.5 |
22.7 |
Select the last value from field keys that match a regular expression
SELECT LAST(/^[th]/) FROM home
time |
last_hum |
last_temp |
1970-01-01T00:00:00Z |
36.5 |
22.7 |
Select the last value from a field within time windows (grouped by time)
SELECT
LAST(temp)
FROM home
WHERE
room = 'Kitchen'
AND time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)
time |
last |
2022-01-01T06:00:00Z |
22.4 |
2022-01-01T12:00:00Z |
22.7 |
2022-01-01T18:00:00Z |
22.7 |
Notice that when grouping by time, LAST()
overrides the point’s original timestamp.
MAX()
Returns the greatest field value.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports numeric fields.
Notable behaviors
Examples
Select the maximum value from a field
time |
max |
2022-01-01T20:00:00Z |
26 |
Select the maximum value from each field
time |
max_co |
max_hum |
max_temp |
1970-01-01T00:00:00Z |
26 |
36.9 |
23.3 |
Select the maximum value from field keys that match a regular expression
SELECT MAX(/^[th]/) FROM home
time |
max_hum |
max_temp |
1970-01-01T00:00:00Z |
36.9 |
23.3 |
Select the maximum value from a field within time windows (grouped by time)
SELECT
MAX(temp)
FROM home
WHERE
room = 'Kitchen'
AND time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)
time |
max |
2022-01-01T06:00:00Z |
23 |
2022-01-01T12:00:00Z |
22.8 |
2022-01-01T18:00:00Z |
23.3 |
Notice that when grouping by time, MAX()
overrides the point’s original timestamp.
MIN()
Returns the lowest field value.
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports numeric fields.
Notable behaviors
Examples
Select the minimum value from a field
SELECT MIN(temp) FROM home
time |
min |
2022-01-01T08:00:00Z |
21 |
Select the minimum value from each field
time |
min_co |
min_hum |
min_temp |
1970-01-01T00:00:00Z |
0 |
35.9 |
21 |
Select the minimum value from field keys that match a regular expression
SELECT MIN(/^[th]/) FROM home
time |
min_hum |
min_temp |
1970-01-01T00:00:00Z |
35.9 |
21 |
Select the minimum value from a field within time windows (grouped by time)
SELECT
MIN(temp)
FROM home
WHERE
room = 'Kitchen'
AND time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)
time |
min |
2022-01-01T06:00:00Z |
21 |
2022-01-01T12:00:00Z |
22.4 |
2022-01-01T18:00:00Z |
22.7 |
Notice that when grouping by time, MIN()
overrides the point’s original timestamp.
PERCENTILE()
Returns the N
th percentile field value.
PERCENTILE(field_expression, N)
Arguments
- field_expression: Expression to identify one or more fields to operate on.
Can be a field key,
constant, regular expression, or wildcard (
*
).
Supports numeric fields.
- N: Percentile to return.
Must be an integer or float value greater than 0 and less than or equal to 100.
Notable behaviors
Examples
Select the 50th percentile value from a field
SELECT PERCENTILE(temp, 50) FROM home
time |
percentile |
2022-01-01T11:00:00Z |
22.4 |
Select the 50th percentile value from each field
SELECT PERCENTILE(*, 50) FROM home
time |
percentile_co |
percentile_hum |
percentile_temp |
1970-01-01T00:00:00Z |
1 |
36 |
22.4 |
Select the 50th percentile value from field keys that match a regular expression
SELECT PERCENTILE(/^[th]/, 50) FROM home
time |
percentile_hum |
percentile_temp |
1970-01-01T00:00:00Z |
36 |
22.4 |
Select the 50th percentile value from a field within time windows (grouped by time)
SELECT
PERCENTILE(temp, 50)
FROM home
WHERE
room = 'Kitchen'
AND time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T20:00:00Z'
GROUP BY time(6h)
time |
percentile |
2022-01-01T06:00:00Z |
22.4 |
2022-01-01T12:00:00Z |
22.7 |
2022-01-01T18:00:00Z |
23.1 |
Notice that when grouping by time, PERCENTILE()
overrides the point’s original timestamp.
TOP()
Returns the greatest N
field values.
TOP()
supports int64 and float64 field value data types.
TOP(field_expression[, tag_expression_1[, ..., tag_expression_n]], N)
Note: TOP()
returns the field value with the earliest timestamp if there’s
a tie between two or more values for the greatest value.
Arguments
- field_expression: Expression to identify the field to operate on.
Can be a field key
or constant.
- tag_expression: Expression to identify a tag key to segment by.
Can be a tag key
or constant. Comma-delimit multiple tags.
- N: Number of results to return from each InfluxQL group or specified tag segment.
Notable behaviors
Examples
Select the top three values of a field
SELECT TOP(temp, 3) FROM home
time |
top |
2022-01-01T09:00:00Z |
23 |
2022-01-01T18:00:00Z |
23.3 |
2022-01-01T19:00:00Z |
23.1 |
Select the top field value for two unique tag values
SELECT TOP(temp, room, 2) FROM home
time |
top |
room |
2022-01-01T18:00:00Z |
23.3 |
Kitchen |
2022-01-01T18:00:00Z |
22.8 |
Living Room |
Select the top three field values and the tag value associated with each
SELECT TOP(temp, 3), room FROM home
time |
top |
room |
2022-01-01T09:00:00Z |
23 |
Kitchen |
2022-01-01T18:00:00Z |
23.3 |
Kitchen |
2022-01-01T19:00:00Z |
23.1 |
Kitchen |
Select the top field values for unique tag values and within time windows (grouped by time)
SELECT
TOP(temp, room, 2)
FROM home
WHERE
time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T12:00:00Z'
GROUP BY time(2h)
time |
top |
room |
2022-01-01T09:00:00Z |
23 |
Kitchen |
2022-01-01T09:00:00Z |
21.4 |
Living Room |
2022-01-01T10:00:00Z |
22.7 |
Kitchen |
2022-01-01T11:00:00Z |
22.2 |
Living Room |
2022-01-01T12:00:00Z |
22.5 |
Kitchen |
2022-01-01T12:00:00Z |
22.2 |
Living Room |
Notice that when grouping by time, TOP()
maintains the point’s original timestamp.
Notable behaviors of selector functions
Timestamps when grouping by time
When using selector functions with a GROUP BY time()
clause, most selector
functions return the timestamp of the starting boundary for each time interval.
However functions with an N
argument that specifies the number of results to
return per group maintain the original timestamp of each returned point.
Return the start time of each time interval
Maintain the original timestamp
Selector functions may return fewer points than expected
Queries that use the following selector functions with an N
argument may
return fewer points than expected.
If the InfluxQL group or specified tag key contains X
points or unique tag
values and X
is less than N
, the function returns X
results instead of N
for each group or tag value.
View example for FN(field_key, N)
The example below selects the bottom 5 temperatures from the Kitchen between
2022-01-01T08:00:00Z
and 2022-01-01T10:00:00Z.
There are only 3 points recorded for the Kitchen during the queried time range,
so the query returns 3 points instead of 5.
SELECT BOTTOM(temp, 5)
FROM home
WHERE
time >= '2022-01-01T08:00:00Z'
AND time <= '2022-01-01T10:00:00Z'
AND room = 'Kitchen'
time |
bottom |
2022-01-01T08:00:00Z |
21 |
2022-01-01T09:00:00Z |
23 |
2022-01-01T10:00:00Z |
22.7 |
View example for FN(field_key, tag_key, N)
The example below selects the top temperature from 3 unique values of the room
tag.
However, the room
tag only has 2 unique values, so results only contain 2 values.
SELECT TOP(temp, room, 3) FROM home
time |
top |
room |
2022-01-01T18:00:00Z |
23.3 |
Kitchen |
2022-01-01T18:00:00Z |
22.8 |
Living Room |
Was this page helpful?
Thank you for your feedback!
Support and feedback
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:
Customers with an annual or support contract can contact InfluxData Support.