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()
• FIRST()
• LAST()
• MAX()
• MIN()
• PERCENTILE()
• SAMPLE()
• TOP()

BOTTOM()

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

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 BOTTOM("water_level",3) FROM "h2o_feet"

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

SELECT BOTTOM("water_level",4),"location","level description" FROM "h2o_feet"

SELECT BOTTOM("water_level",3),"location" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:54:00Z' GROUP BY time(24m) ORDER BY time DESC

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)

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"

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 FIRST("level description") FROM "h2o_feet"

SELECT FIRST(*) FROM "h2o_feet"

SELECT FIRST(/level/) FROM "h2o_feet"

SELECT FIRST("level description"),"location","water_level" FROM "h2o_feet"

SELECT FIRST("water_level") FROM "h2o_feet" WHERE time >= '2019-08-17T23:48:00Z' AND time <= '2019-08-18T00:54:00Z' GROUP BY time(12m),* fill(9.01) LIMIT 4 SLIMIT 1

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 LAST("level description") FROM "h2o_feet"

SELECT LAST(*) FROM "h2o_feet"

SELECT LAST(/level/) FROM "h2o_feet"

SELECT LAST("level description"),"location","water_level" FROM "h2o_feet"

SELECT LAST("water_level") FROM "h2o_feet" WHERE time >= '2019-08-17T23:48:00Z' AND time <= '2019-08-18T00:54:00Z' GROUP BY time(12m),* fill(9.01) LIMIT 4 SLIMIT 1

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 MAX("water_level") FROM "h2o_feet"

SELECT MAX(*) FROM "h2o_feet"

SELECT MAX(/level/) FROM "h2o_feet"

SELECT MAX("water_level"),"location","level description" FROM "h2o_feet"

SELECT MAX("water_level") FROM "h2o_feet" WHERE time >= '2019-08-17T23:48:00Z' AND time <= '2019-08-18T00:54:00Z' GROUP BY time(12m),* fill(9.01) LIMIT 4 SLIMIT 1

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 MIN("water_level") FROM "h2o_feet"

SELECT MIN(*) FROM "h2o_feet"

SELECT MIN(/level/) FROM "h2o_feet"

SELECT MIN("water_level"),"location","level description" FROM "h2o_feet"

SELECT MIN("water_level") FROM "h2o_feet" WHERE time >= '2019-08-17T23:48:00Z' AND time <= '2019-08-18T00:54:00Z' GROUP BY time(12m),* fill(9.01) LIMIT 4 SLIMIT 1

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 PERCENTILE("water_level",5) FROM "h2o_feet"

SELECT PERCENTILE(*,5) FROM "h2o_feet"

SELECT PERCENTILE(/level/,5) FROM "h2o_feet"

SELECT PERCENTILE("water_level",5),"location","level description" FROM "h2o_feet"

SELECT PERCENTILE("water_level",20) FROM "h2o_feet" WHERE time >= '2019-08-17T23:48:00Z' AND time <= '2019-08-18T00:54:00Z' GROUP BY time(24m) fill(15) LIMIT 2

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 SAMPLE("water_level",2) FROM "h2o_feet"

SELECT SAMPLE(*,2) FROM "h2o_feet"

SELECT SAMPLE(/level/,2) FROM "h2o_feet"

SELECT SAMPLE("water_level",2),"location","level description" FROM "h2o_feet"

SELECT SAMPLE("water_level",1) 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)

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)

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.

Examples

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

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

SELECT TOP("water_level",4),"location","level description" FROM "h2o_feet"

SELECT TOP("water_level",3),"location" FROM "h2o_feet" WHERE time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:54:00Z' GROUP BY time(24m) ORDER BY time DESC

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)

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"