Selector functions-阿里云帮助中心

Selector functions return one or more field values based on a selection criterion—such as rank, timestamp, percentile, or random sampling. Unlike aggregation functions, selector functions generally preserve the original timestamps of returned data points. When used with GROUP BY time(), some selectors—such as FIRST(), LAST(), MAX(), MIN(), and PERCENTILE()—return the timestamp that marks the start of the time interval rather than the original data point timestamp.

Function	Returns
BOTTOM()	The N smallest field values
FIRST()	The field value with the earliest timestamp
LAST()	The field value with the latest timestamp
MAX()	The largest field value
MIN()	The smallest field value
PERCENTILE()	The field value at the Nth percentile
SAMPLE()	N randomly sampled field values
TOP()	The N largest field values

BOTTOM()

Returns the N smallest field values.

Syntax

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

Supported data types: INT64 and FLOAT64.

When two or more values tie for the smallest, BOTTOM() returns the one with the earliest timestamp. When used with an INTO clause, BOTTOM() handles timestamps differently from other InfluxQL functions—see BOTTOM() with an INTO clause.

Description

Syntax variant	Behavior
`BOTTOM(field_key,N)`	Returns the N smallest values of the specified field key
`BOTTOM(field_key,tag_key(s),N)`	Returns the smallest value per tag, for each of the N tag values of the specified tag key
`BOTTOM(field_key,N),tag_key(s),field_key(s)`	Returns the N smallest values, plus the associated tag and field values

Example 1: Return the three smallest values of a field key

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

name: h2o_feet
time                   bottom
----------
2015-08-29T14:30:00Z   -0.61
2015-08-29T14:36:00Z   -0.591
2015-08-30T15:18:00Z   -0.594

Example 2: Return the smallest value per tag, across two tag values

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

name: h2o_feet
time                   bottom   location
------------------
2015-08-29T10:36:00Z   -0.243   santa_monica
2015-08-29T14:30:00Z   -0.61    coyote_creek

Returns the smallest water_level value for each of the two location tag values.

Example 3: Return the four smallest values along with related tag and field values

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

name: h2o_feet
time                   bottom   location      level description
-----------------------------------
2015-08-29T14:24:00Z   -0.587   coyote_creek  below 3 feet
2015-08-29T14:30:00Z   -0.61    coyote_creek  below 3 feet
2015-08-29T14:36:00Z   -0.591   coyote_creek  below 3 feet
2015-08-30T15:18:00Z   -0.594   coyote_creek  below 3 feet

Example 4: Return the three smallest values per time interval using multiple clauses

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

name: h2o_feet
time                   bottom   location
------------------
2015-08-18T00:48:00Z   1.991    santa_monica
2015-08-18T00:54:00Z   2.054    santa_monica
2015-08-18T00:54:00Z   6.982    coyote_creek
2015-08-18T00:24:00Z   2.041    santa_monica
2015-08-18T00:30:00Z   2.051    santa_monica
2015-08-18T00:42:00Z   2.057    santa_monica
2015-08-18T00:00:00Z   2.064    santa_monica
2015-08-18T00:06:00Z   2.116    santa_monica
2015-08-18T00:12:00Z   2.028    santa_monica

Returns the three smallest water_level values within each 24-minute interval from 2015-08-18T00:00:00Z to 2015-08-18T00:54:00Z, sorted in descending timestamp order.

GROUP BY time() does not overwrite the original timestamps. Each returned timestamp reflects the actual data point time, not the interval start time.

Common issues

BOTTOM() with GROUP BY time()

When BOTTOM() is combined with GROUP BY time(), the query returns the N smallest points per time interval—and the original timestamps of those points are preserved. This differs from most functions that use GROUP BY time(), where each returned timestamp marks the start of the time interval.

Example: The following query returns the two smallest points per 18-minute interval. The returned timestamps are the original data point timestamps, not the interval boundaries.

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

name: h2o_feet
time                   bottom
----------
                               __
2015-08-18T00:00:00Z   2.064  |
2015-08-18T00:12:00Z   2.028  |<--- Smallest points for the first time interval
                               --
                               __
2015-08-18T00:24:00Z   2.041  |
2015-08-18T00:30:00Z   2.051  |<--- Smallest points for the second time interval
                               --

BOTTOM() with fewer tag values than N

When using BOTTOM(field_key,tag_key,N), if the tag key has fewer than N distinct values, the query returns fewer points than N. The query returns one point per available tag value.

Example: The following query requests the smallest value for each of three location tag values, but location has only two values (santa_monica and coyote_creek), so the query returns two points.

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

name: h2o_feet
time                   bottom   location
------------------
2015-08-29T10:36:00Z   -0.243   santa_monica
2015-08-29T14:30:00Z   -0.61    coyote_creek

BOTTOM() with an INTO clause

When using an INTO clause without GROUP BY tag, most InfluxQL functions convert tags to fields in the written output. BOTTOM(field_key,tag_key(s),N) is an exception—the specified tag keys are preserved as tags in the output measurement.

Example: The following query writes the two smallest water_level values (one per location tag) to bottom_water_levels. The location tag is retained.

> SELECT BOTTOM("water_level","location",2) INTO "bottom_water_levels" FROM "h2o_feet"

name: result
time                   written
-----------
1970-01-01T00:00:00Z   2

> SHOW TAG KEYS FROM "bottom_water_levels"

name: bottom_water_levels
tagKey
------
location

FIRST()

Returns the field value with the earliest timestamp.

Syntax

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

Supported data types: All field value types.

Description

Syntax variant	Behavior
`FIRST(field_key)`	Returns the value with the earliest timestamp for the specified field key
`FIRST(/regular_expression/)`	Returns the earliest-timestamped value for each matching field key
`FIRST(*)`	Returns the earliest-timestamped value for every field key in the measurement
`FIRST(field_key),tag_key(s),field_key(s)`	Returns the earliest-timestamped value plus associated tag and field values

Example 1: Return the earliest value for a field key

SELECT FIRST("level description") FROM "h2o_feet"

name: h2o_feet
time                   first
---------
2015-08-18T00:00:00Z   between 6 and 9 feet

Example 2: Return the earliest value for every field key in a measurement

SELECT FIRST(*) FROM "h2o_feet"

name: h2o_feet
time                   first_level description   first_water_level
--------------------------------------------
1970-01-01T00:00:00Z   between 6 and 9 feet      8.12

Returns the earliest-timestamped value for level description and water_level, the two field keys in h2o_feet.

Example 3: Return the earliest value for field keys matching a regular expression

SELECT FIRST(/level/) FROM "h2o_feet"

name: h2o_feet
time                   first_level description   first_water_level
--------------------------------------------
1970-01-01T00:00:00Z   between 6 and 9 feet      8.12

Returns the earliest-timestamped value for each field key containing level in h2o_feet.

Example 4: Return the earliest value along with related tag and field values

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

name: h2o_feet
time                   first                  location      water_level
----------------------------
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek  8.12

Example 5: Return the earliest value per time interval using multiple clauses

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

name: h2o_feet
tags: location=coyote_creek
time                   first
---------
2015-08-17T23:48:00Z   9.01
2015-08-18T00:00:00Z   8.12
2015-08-18T00:12:00Z   7.887
2015-08-18T00:24:00Z   7.635

Returns the earliest water_level value within each 12-minute interval from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z, grouped by tag. Intervals with no data return 9.01 from fill(). Results are limited to 4 points and 1 series.

GROUP BY time() overwrites the original timestamps. Each returned timestamp marks the start of a 12-minute interval—the first interval runs from 2015-08-17T23:48:00Z to 2015-08-18T00:00:00Z, and the last runs from 2015-08-18T00:24:00Z to 2015-08-18T00:36:00Z.

LAST()

Returns the field value with the latest timestamp.

Syntax

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

Supported data types: All field value types.

Description

Syntax variant	Behavior
`LAST(field_key)`	Returns the value with the latest timestamp for the specified field key
`LAST(/regular_expression/)`	Returns the latest-timestamped value for each matching field key
`LAST(*)`	Returns the latest-timestamped value for every field key in the measurement
`LAST(field_key),tag_key(s),field_key(s)`	Returns the latest-timestamped value plus associated tag and field values

Example 1: Return the latest value for a field key

SELECT LAST("level description") FROM "h2o_feet"

name: h2o_feet
time                   last
--------
2015-09-18T21:42:00Z   between 3 and 6 feet

Example 2: Return the latest value for every field key in a measurement

SELECT LAST(*) FROM "h2o_feet"

name: h2o_feet
time                   last_level description   last_water_level
--------------------------------------------
2015-09-18T21:42:00Z   between 3 and 6 feet     4.938

Returns the latest-timestamped value for level description and water_level, the two field keys in h2o_feet.

Example 3: Return the latest value for field keys matching a regular expression

SELECT LAST(/level/) FROM "h2o_feet"

name: h2o_feet
time                   last_level description   last_water_level
--------------------------------------------
2015-09-18T21:42:00Z   between 3 and 6 feet     4.938

Returns the latest-timestamped value for each field key containing level in h2o_feet.

Example 4: Return the latest value along with related tag and field values

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

name: h2o_feet
time                   last                   location      water_level
---------------------------
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica  4.938

Example 5: Return the latest value per time interval using multiple clauses

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

name: h2o_feet
tags: location=coyote_creek
time                   last
--------
2015-08-17T23:48:00Z   9.01
2015-08-18T00:00:00Z   8.005
2015-08-18T00:12:00Z   7.762
2015-08-18T00:24:00Z   7.5

Returns the latest water_level value within each 12-minute interval from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z, grouped by tag. Intervals with no data return 9.01 from fill(). Results are limited to 4 points and 1 series.

GROUP BY time() overwrites the original timestamps. Each returned timestamp marks the start of a 12-minute interval—the first interval runs from 2015-08-17T23:48:00Z to 2015-08-18T00:00:00Z, and the last runs from 2015-08-18T00:24:00Z to 2015-08-18T00:36:00Z.

MAX()

Returns the largest field value.

Syntax

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

Supported data types: INT64 and FLOAT64.

Description

Syntax variant	Behavior
`MAX(field_key)`	Returns the largest value of the specified field key
`MAX(/regular_expression/)`	Returns the largest value for each matching field key
`MAX(*)`	Returns the largest value for every numeric field key in the measurement
`MAX(field_key),tag_key(s),field_key(s)`	Returns the largest value plus associated tag and field values

Example 1: Return the largest value for a field key

SELECT MAX("water_level") FROM "h2o_feet"

name: h2o_feet
time                   max
-------
2015-08-29T07:24:00Z   9.964

Example 2: Return the largest value for every numeric field key in a measurement

SELECT MAX(*) FROM "h2o_feet"

name: h2o_feet
time                   max_water_level
-------------------
2015-08-29T07:24:00Z   9.964

h2o_feet contains only one numeric field, water_level.

Example 3: Return the largest value for field keys matching a regular expression

SELECT MAX(/water/) FROM "h2o_feet"

name: h2o_feet
time                   max_water_level
-------------------
2015-08-29T07:24:00Z   9.964

Returns the largest value for each numeric field key containing water in h2o_feet.

Example 4: Return the largest value along with related tag and field values

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

name: h2o_feet
time                   max     location      level description
--------------------------------
2015-08-29T07:24:00Z   9.964   coyote_creek  at or greater than 9 feet

Example 5: Return the largest value per time interval using multiple clauses

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

name: h2o_feet
tags: location=coyote_creek
time                   max
-------
2015-08-17T23:48:00Z   9.01
2015-08-18T00:00:00Z   8.12
2015-08-18T00:12:00Z   7.887
2015-08-18T00:24:00Z   7.635

Returns the largest water_level value within each 12-minute interval from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z, grouped by tag. Intervals with no data return 9.01 from fill(). Results are limited to 4 points and 1 series.

GROUP BY time() overwrites the original timestamps. Each returned timestamp marks the start of a 12-minute interval—the first interval runs from 2015-08-17T23:48:00Z to 2015-08-18T00:00:00Z, and the last runs from 2015-08-18T00:24:00Z to 2015-08-18T00:36:00Z.

MIN()

Returns the smallest field value.

Syntax

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

Supported data types: INT64 and FLOAT64.

Description

Syntax variant	Behavior
`MIN(field_key)`	Returns the smallest value of the specified field key
`MIN(/regular_expression/)`	Returns the smallest value for each matching field key
`MIN(*)`	Returns the smallest value for every numeric field key in the measurement
`MIN(field_key),tag_key(s),field_key(s)`	Returns the smallest value plus associated tag and field values

Example 1: Return the smallest value for a field key

SELECT MIN("water_level") FROM "h2o_feet"

name: h2o_feet
time                   min
-------
2015-08-29T14:30:00Z   -0.61

Example 2: Return the smallest value for every numeric field key in a measurement

SELECT MIN(*) FROM "h2o_feet"

name: h2o_feet
time                   min_water_level
-------------------
2015-08-29T14:30:00Z   -0.61

h2o_feet contains only one numeric field, water_level.

Example 3: Return the smallest value for field keys matching a regular expression

SELECT MIN(/water/) FROM "h2o_feet"

name: h2o_feet
time                   min_water_level
-------------------
2015-08-29T14:30:00Z   -0.61

Returns the smallest value for each numeric field key containing water in h2o_feet.

Example 4: Return the smallest value along with related tag and field values

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

name: h2o_feet
time                   min     location      level description
--------------------------------
2015-08-29T14:30:00Z   -0.61   coyote_creek  below 3 feet

Example 5: Return the smallest value per time interval using multiple clauses

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

name: h2o_feet
tags: location=coyote_creek
time                   min
-------
2015-08-17T23:48:00Z   9.01
2015-08-18T00:00:00Z   8.005
2015-08-18T00:12:00Z   7.762
2015-08-18T00:24:00Z   7.5

Returns the smallest water_level value within each 12-minute interval from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z, grouped by tag. Intervals with no data return 9.01 from fill(). Results are limited to 4 points and 1 series.

GROUP BY time() overwrites the original timestamps. Each returned timestamp marks the start of a 12-minute interval—the first interval runs from 2015-08-17T23:48:00Z to 2015-08-18T00:00:00Z, and the last runs from 2015-08-18T00:24:00Z to 2015-08-18T00:36:00Z.

PERCENTILE()

Returns the field value at the Nth percentile.

Syntax

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

Supported data types: INT64 and FLOAT64.

Parameters: N must be an integer or floating-point number between 0 and 100 (inclusive).

Description

Syntax variant	Behavior
`PERCENTILE(field_key,N)`	Returns the Nth percentile value of the specified field key
`PERCENTILE(/regular_expression/,N)`	Returns the Nth percentile value for each matching field key
`PERCENTILE(*,N)`	Returns the Nth percentile value for every field key in the measurement
`PERCENTILE(field_key,N),tag_key(s),field_key(s)`	Returns the Nth percentile value plus associated tag and field values

Example 1: Return the fifth percentile value for a field key

SELECT PERCENTILE("water_level",5) FROM "h2o_feet"

name: h2o_feet
time                   percentile
--------------
2015-08-31T03:42:00Z   1.122

Returns the smallest water_level value that is larger than 5% of all values in h2o_feet.

Example 2: Return the fifth percentile value for every numeric field key in a measurement

SELECT PERCENTILE(*,5) FROM "h2o_feet"

name: h2o_feet
time                   percentile_water_level
--------------------------
2015-08-31T03:42:00Z   1.122

Example 3: Return the fifth percentile value for field keys matching a regular expression

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

name: h2o_feet
time                   percentile_water_level
--------------------------
2015-08-31T03:42:00Z   1.122

Returns the fifth percentile value for each numeric field key containing water in h2o_feet.

Example 4: Return the fifth percentile value along with related tag and field values

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

name: h2o_feet
time                   percentile   location      level description
---------------------------------------
2015-08-31T03:42:00Z   1.122        coyote_creek  below 3 feet

Example 5: Return the twentieth percentile value per time interval using multiple clauses

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

name: h2o_feet
time                   percentile
--------------
2015-08-17T23:36:00Z   15
2015-08-18T00:00:00Z   2.064

Returns the 20th percentile water_level value within each 24-minute interval. Intervals with no data return 15 from fill(). Results are limited to 2 points.

GROUP BY time() overwrites the original timestamps. The first interval runs from 2015-08-17T23:36:00Z to 2015-08-18T00:00:00Z, and the second runs from 2015-08-18T00:00:00Z to 2015-08-18T00:24:00Z.

Relationships with other functions

PERCENTILE(field_key,100) is equivalent to MAX(field_key).
PERCENTILE(field_key,50) is similar to MEDIAN(field_key), except when the field has an even number of values—in that case, MEDIAN() returns the average of the two middle values.
PERCENTILE(field_key,0) returns null, not the same result as MIN(field_key).

SAMPLE()

Returns N randomly sampled field values using reservoir sampling.

Syntax

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

Supported data types: All field value types.

Parameters: N must be an integer.

Description

Syntax variant	Behavior
`SAMPLE(field_key,N)`	Returns N randomly sampled values of the specified field key
`SAMPLE(/regular_expression/,N)`	Returns N random values for each matching field key
`SAMPLE(*,N)`	Returns N random values for every field key in the measurement
`SAMPLE(field_key,N),tag_key(s),field_key(s)`	Returns N random values plus associated tag and field values

Example 1: Return two random values for a field key

SELECT SAMPLE("water_level",2) FROM "h2o_feet"

name: h2o_feet
time                   sample
----------
2015-09-09T21:48:00Z   5.659
2015-09-18T10:00:00Z   6.939

Example 2: Return two random values for every field key in a measurement

SELECT SAMPLE(*,2) FROM "h2o_feet"

name: h2o_feet
time                   sample_level description   sample_water_level
----------------------------------------------
2015-08-25T17:06:00Z   3.284
2015-09-03T04:30:00Z   below 3 feet
2015-09-03T20:06:00Z   between 3 and 6 feet
2015-09-08T21:54:00Z   3.412

Returns two random values for level description and water_level, the two field keys in h2o_feet.

Example 3: Return two random values for field keys matching a regular expression

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

name: h2o_feet
time                   sample_level description   sample_water_level
----------------------------------------------
2015-08-30T05:54:00Z   between 6 and 9 feet
2015-09-07T01:18:00Z   7.854
2015-09-09T20:30:00Z   7.32
2015-09-13T19:18:00Z   between 3 and 6 feet

Returns two random values for each field key containing level in h2o_feet.

Example 4: Return two random values along with related tag and field values

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

name: h2o_feet
time                   sample   location      level description
-----------------------------------
2015-08-29T10:54:00Z   5.689    coyote_creek  between 3 and 6 feet
2015-09-08T15:48:00Z   6.391    coyote_creek  between 6 and 9 feet

Example 5: Return one random value per time interval using multiple clauses

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

name: h2o_feet
time                   sample
----------
2015-08-18T00:12:00Z   2.028
2015-08-18T00:30:00Z   2.051

Returns one random water_level value per 18-minute interval from 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z.

GROUP BY time() does not overwrite the original timestamps. Each returned timestamp reflects the actual data point time, not the interval start time.

Common issues

SAMPLE() with GROUP BY time()

When SAMPLE() is combined with GROUP BY time(), the query returns N randomly selected points per time interval—and the original timestamps of those points are preserved. This differs from most functions that use GROUP BY time(), where each returned timestamp marks the start of the time interval.

Example: The following query returns two randomly selected points per 18-minute interval. The returned timestamps are the original data point timestamps.

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

name: h2o_feet
time                   sample
----------
                               __
2015-08-18T00:06:00Z   2.116  |
2015-08-18T00:12:00Z   2.028  |<--- Randomly-selected points for the first time interval
                               --
                               __
2015-08-18T00:18:00Z   2.126  |
2015-08-18T00:30:00Z   2.051  |<--- Randomly-selected points for the second time interval
                               --

TOP()

Returns the N largest field values.

Syntax

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

Supported data types: INT64 and FLOAT64.

When two or more values tie for the largest, TOP() returns the one with the earliest timestamp. When used with an INTO clause, TOP() handles timestamps differently from other InfluxQL functions—see TOP() with an INTO clause.

Description

Syntax variant	Behavior
`TOP(field_key,N)`	Returns the N largest values of the specified field key
`TOP(field_key,tag_key(s),N)`	Returns the largest value per tag, for each of the N tag values of the specified tag key
`TOP(field_key,N),tag_key(s),field_key(s)`	Returns the N largest values plus associated tag and field values

Example 1: Return the three largest values of a field key

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

name: h2o_feet
time                   top
-------
2015-08-29T07:18:00Z   9.957
2015-08-29T07:24:00Z   9.964
2015-08-29T07:30:00Z   9.954

Example 2: Return the largest value per tag, across two tag values

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

name: h2o_feet
time                   top     location
---------------
2015-08-29T03:54:00Z   7.205   santa_monica
2015-08-29T07:24:00Z   9.964   coyote_creek

Returns the largest water_level value for each of the two location tag values.

Example 3: Return the four largest values along with related tag and field values

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

name: h2o_feet
time                   top     location      level description
--------------------------------
2015-08-29T07:18:00Z   9.957   coyote_creek  at or greater than 9 feet
2015-08-29T07:24:00Z   9.964   coyote_creek  at or greater than 9 feet
2015-08-29T07:30:00Z   9.954   coyote_creek  at or greater than 9 feet
2015-08-29T07:36:00Z   9.941   coyote_creek  at or greater than 9 feet

Example 4: Return the three largest values per time interval using multiple clauses

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

name: h2o_feet
time                   top     location
---------------
2015-08-18T00:48:00Z   7.11    coyote_creek
2015-08-18T00:54:00Z   6.982   coyote_creek
2015-08-18T00:54:00Z   2.054   santa_monica
2015-08-18T00:24:00Z   7.635   coyote_creek
2015-08-18T00:30:00Z   7.5     coyote_creek
2015-08-18T00:36:00Z   7.372   coyote_creek
2015-08-18T00:00:00Z   8.12    coyote_creek
2015-08-18T00:06:00Z   8.005   coyote_creek
2015-08-18T00:12:00Z   7.887   coyote_creek

Returns the three largest water_level values per 24-minute interval from 2015-08-18T00:00:00Z to 2015-08-18T00:54:00Z, sorted in descending timestamp order.

GROUP BY time() does not overwrite the original timestamps. Each returned timestamp reflects the actual data point time, not the interval start time.

Common issues

TOP() with GROUP BY time()

When TOP() is combined with GROUP BY time(), the query returns the N largest points per time interval—and the original timestamps of those points are preserved. This differs from most functions that use GROUP BY time(), where each returned timestamp marks the start of the time interval.

Example: The following query returns the two largest points per 18-minute interval. The returned timestamps are the original data point timestamps.

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

name: h2o_feet
time                   top
----------
                               __
2015-08-18T00:00:00Z   2.064  |
2015-08-18T00:06:00Z   2.116  |<--- Greatest points for the first time interval
                               --
                               __
2015-08-18T00:18:00Z   2.126  |
2015-08-18T00:30:00Z   2.051  |<--- Greatest points for the second time interval
                               --

TOP() with fewer tag values than N

When using TOP(field_key,tag_key,N), if the tag key has fewer than N distinct values, the query returns fewer points than N. The query returns one point per available tag value.

Example: The following query requests the largest value for each of three location tag values, but location has only two values (santa_monica and coyote_creek), so the query returns two points.

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

name: h2o_feet
time                   top     location
---------------
2015-08-29T03:54:00Z   7.205   santa_monica
2015-08-29T07:24:00Z   9.964   coyote_creek

TOP() with an INTO clause

When using an INTO clause without GROUP BY tag, most InfluxQL functions convert tags to fields in the written output. TOP(field_key,tag_key(s),N) is an exception—the specified tag keys are preserved as tags in the output measurement.

Example: The following query writes the two largest water_level values (one per location tag) to top_water_levels. The location tag is retained.

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

name: result
time                   written
-----------
1970-01-01T00:00:00Z   2

> SHOW TAG KEYS FROM "top_water_levels"

name: top_water_levels
tagKey
------
location