All Products
Search

This Product

    Time Series Database:Selector functions

    Document Center

    Time Series Database:Selector functions

    Last Updated:Jul 28, 2021

    This topic describes the syntax and parameters of selector functions. It also provides multiple examples.

    BOTTOM()

    Returns the smallest N 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]

    Description

    BOTTOM(field_key,N): returns the smallest N field values of the specified field key.

    BOTTOM(field_key,tag_key(s),N): returns the smallest field value of the specified field key that is related to the N tag values of the specified tag key.

    BOTTOM(field_key,N),tag_key(s),field_key(s): returns the smallest N field values of the specified field key in parentheses, and the related tag and field if applicable.

    BOTTOM(): supports INT64 and FLOAT64 field values.

    Note

    If two or more duplicate smallest values exist, the BOTTOM() function returns the field value that is attached with the earliest timestamp. If the BOTTOM() function is used in combination with an INTO clause, the BOTTOM() function differs from other InfluxQL functions in terms of the returned timestamps.

    Example 1: Obtain the smallest three field values of the specified 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

    This query returns the smallest three field values of the water_level field key in the h2o_feet measurement.

    Example 2: Obtain the smallest field value of a field key that is related to 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

    This query returns the smallest field value of the water_level field key for each of the two tag values of the location tag key.

    Example 3: Obtain the smallest four field values of the specified field key, and the 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

    This query returns the smallest four field values of the water_level field key. This query also returns the values of the location tag key and the level description field key.

    Example 4: Obtain the smallest three field values of the specified field key for each time interval by using a query that includes 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:00Z1.991   santa_monica
2015-08-18T00:54:00Z2.054   santa_monica
2015-08-18T00:54:00Z6.982   coyote_creek
2015-08-18T00:24:00Z2.041   santa_monica
2015-08-18T00:30:00Z2.051   santa_monica
2015-08-18T00:42:00Z2.057   santa_monica
2015-08-18T00:00:00Z2.064   santa_monica
2015-08-18T00:06:00Z2.116   santa_monica
2015-08-18T00:12:00Z2.028   santa_monica

    This query returns the smallest three values of the water_level field key within each 24-minute interval of the range from 2015-08-18T00:00:00Z to 2015-08-18T00:54:00Z. The query results are sorted in descending order based on timestamps.

    Note

    The GROUP BY time() clause does not overwrite the original timestamps of points.

    FAQ

    What are the impacts if I use BOTTOM() functions and GROUP BY time() clauses in queries?

    If you use BOTTOM() functions and GROUP BY time() clauses in queries, the queries return the specified number of points based on the time intervals that are specified by the GROUP BY time() clauses. For the most queries that include GROUP BY time() clauses, each returned timestamp indicates the start time of each time interval that is specified by the GROUP BY time() clauses. However, for the queries that use BOTTOM() functions and GROUP BY time() clauses, the original timestamps of the points are retained.

    Example:The following query returns two random points based on the 18-minute interval that is specified by the GROUP BY time() clause. Note: The original timestamps of the points are returned. The original timestamps do not need to match the start time of the time interval that is specified by the GROUP BY time() clause. 

    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:00Z2.064|
2015-08-18T00:12:00Z2.028|<-------Smallest points for the first time interval
--
                           __
2015-08-18T00:24:00Z2.041|
2015-08-18T00:30:00Z2.051|<-------Smallest points for the second time interval
--

    What are the impacts if the number of tag values for the specified tag key is less than the value of N that is specified in the BOTTOM() function?

    The queries that use the SELECT BOTTOM(<field_key>,<tag_key>,<N>) syntax return N points, and N can be less than the number you specify in the queries. A tag key can have X tag values. In this case, if you specify the number of tag values as N in a query and X is smaller than N, the query returns X points.

    Example: The following query requests the smallest field value of the water_level field key for each of the three tag values of the location tag key. The query returns two points because the location tag key contains only two tag values: santa_monica and coyote_creek. 

    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

    What are the impacts if I use INTO clauses and BOTTOM() functions when tag keys are specified in queries?

    If you use INTO clauses and do not use GROUP BY tag clauses in queries, most InfluxQL functions convert the tags in the raw data into the fields in the newly written data. This rule applies to the BOTTOM() function unless tag keys are specified in the BOTTOM() function: BOTTOM(field_key,tag_key(s),N). In these cases, the specified tags are retained in the newly written data.

    Examples: The first query in the following code block returns the smallest field value of the water_level field key for each of the two tag values of the location tag key. The query results are written to the bottom_water_levels measurement. In the second query, TSDB for InfluxDB® retains the location tag in the bottom_water_levels measurement. 

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

name: result
time                 written
-----------
1970-01-01T00:00:00Z2

> SHOW TAG KEYS FROM "bottom_water_levels"

name: bottom_water_levels
tagKey
------
location

    FIRST()

    Returns the field value that is attached 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]

    Description

    FIRST(field_key): returns the field value that is attached with the earliest timestamp for the specified field key.

    FIRST(/regular_expression/): returns the field value attached with the earliest timestamp for each field key that matches the specified regular expression.

    FIRST(*): returns the field value that is attached with the earliest timestamp for each field key in the specified measurement.

    FIRST(field_key),tag_key(s),field_key(s): returns the field value that is attached with the earliest timestamp for the specified field key, and the related tag and field (if applicable).

    FIRST(): supports field values of all data types.

    Example 1: Obtain the field value that is attached with the earliest timestamp for the specified field key

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

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

    This query returns the field value that is attached with the earliest timestamp for the level description field key in the h2o_feet measurement.

    Example 2: Obtain the field value that is attached with the earliest timestamp for each field key in the specified 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

    This query returns the field value that is attached with the earliest timestamp for each field key in the h2o_feet measurement. The h2o_feet measurement contains the level description and water_level field keys.

    Example 3: Obtain the field value attached with the earliest timestamp for each field key that matches the specified 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

    This query returns the field value attached with the earliest timestamp for each field key that containslevel in the h2o_feet measurement.

    Example 4: Obtain the field value attached with the earliest timestamp for the specified field key, and the 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

    This query returns the field value that is attached with the earliest timestamp for the level description field key in the h2o_feet measurement. This query also returns the values of the location tag key and the water_level field key.

    Example 5: Obtain the field value attached with the earliest timestamp in each time interval for the specified field key by using a query that includes 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:00Z9.01
2015-08-18T00:00:00Z8.12
2015-08-18T00:12:00Z7.887
2015-08-18T00:24:00Z7.635

    This query returns the field value that is attached with the latest timestamp in each time interval for the water_level field key in the h2o_feet measurement. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 9.01. The query limits the number of returned points to 4, and number of returned series to 1.

    Note

    The GROUP BY time() clause overwrites the original timestamps of points. Each timestamp in the query results indicates the start time of a 12-minute interval. The time interval that includes the first point starts from 2015-08-17T23:48:00Z to 2015-08-18T00:00:00Z. The time interval that includes the last point starts from 2015-08-18T00:24:00Z to 2015-08-18T00:36:00Z.

    LAST()

    Returns the field value that is attached 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]

    Description

    LAST(field_key): returns the field value that is attached with the latest timestamp for the specified field key.

    LAST(/regular_expression/): returns the field value attached with the latest timestamp for each field key that matches the specified regular expression.

    LAST(*): returns the field value that is attached with the latest timestamp for each field key in the specified measurement.

    LAST(field_key),tag_key(s),field_key(s): returns the field value that is attached with the latest timestamp for the specified field key, and the related tag and field (if applicable).

    LAST(): supports field values of all data types.

    Example 1: Obtain the field value that is attached with the latest timestamp for the specified field key

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

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

    This query returns the field value that is attached with the latest timestamp for the level description field key in the h2o_feet measurement.

    Example 2: Obtain the field value attached with the latest timestamp for each field key in the specified 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

    This query returns the field value that is attached with the latest timestamp for each field key in the h2o_feet measurement. The h2o_feet measurement contains the level description and water_level field keys.

    Example 3: Obtain the field value attached with the latest timestamp for each field key that matches the specified 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

    This query returns the field value attached with the latest timestamp for each field key that contains level in the h2o_feet measurement.

    Example 4: Obtain the field value attached with the latest timestamp for the specified field key, and the 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

    This query returns the field value that is attached with the latest timestamp for the level description field key in the h2o_feet measurement. This query also returns the values of the location tag key and the water_level field key.

    Example 5: Obtain the field value attached with the latest timestamp in each time interval for the specified field key by using a query that includes 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:00Z9.01
2015-08-18T00:00:00Z8.005
2015-08-18T00:12:00Z7.762
2015-08-18T00:24:00Z7.5

    This query returns the field value that is attached with the latest timestamp in each time interval for the water_level field key in the h2o_feet measurement. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals during which no data is reported, the fill() function returns 9.01. The query limits the number of returned points to 4, and number of returned series to 1.

    Note

    The GROUP BY time() clause overwrites the original timestamps of points. Each timestamp in the query results indicates the start time of a 12-minute interval. The time interval that includes the first point starts from 2015-08-17T23:48:00Z to 2015-08-18T00:00:00Z. The time interval that includes the last point starts from 2015-08-18T00:24:00Z to 2015-08-18T00:36:00Z.

    MAX()

    Returns the largest field values of the specified field keys.

    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]

    Description

    MAX(field_key): returns the largest field value of the specified field key.

    MAX(/regular_expression/): returns the largest field value of each field key that matches the specified regular expression.

    MAX(*): returns the largest field value of each field key in the specified measurement.

    MAX(field_key),tag_key(s),field_key(s): returns the largest field value of the specified field key, and the related tag and field (if applicable).

    MAX(): supports INT64 and FLOAT64 field values.

    Example 1: Obtain the largest field value of the specified field key

    SELECT MAX("water_level") FROM "h2o_feet"

name: h2o_feet
time                   max
-------
2015-08-29T07:24:00Z9.964

    This query returns the largest field value of the water_level field key in the h2o_feet measurement.

    Example 2: Obtain the largest field value of each field key in the specified measurement

    SELECT MAX(*) FROM "h2o_feet"

name: h2o_feet
time                   max_water_level
-------------------
2015-08-29T07:24:00Z9.964

    This query returns the largest field value of each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the water_level field of the numeric data type.

    Example 3: Obtain the largest field value of each field key that matches the specified regular expression

    SELECT MAX(/water/) FROM "h2o_feet"

name: h2o_feet
time                   max_water_level
-------------------
2015-08-29T07:24:00Z9.964

    This query returns the largest field value of each field key that contains water and stores numeric values in the water measurement.

    Example 4: Obtain the largest field value of the specified field key, and the 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:00Z9.964  coyote_creek  at or greater than 9 feet

    This query returns the largest field value of the water_level field key in the h2o_feet measurement. This query also returns the values of the location tag key and the level description field key.

    Example 5: Obtain the largest field value of the specified field key for each time interval by using a query that includes 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:00Z9.01
2015-08-18T00:00:00Z8.12
2015-08-18T00:12:00Z7.887
2015-08-18T00:24:00Z7.635

    This query returns the largest field value of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals in which no data is reported, the fill() function returns 9.01. The query limits the number of returned points to 4, and number of returned series to 1.

    Note

    The GROUP BY time() clause overwrites the original timestamps of points. Each timestamp in the query results indicates the start time of a 12-minute interval. The time interval that includes the first point starts from 2015-08-17T23:48:00Z to 2015-08-18T00:00:00Z. The time interval that includes the first point starts from 2015-08-18T00:24:00Z to 2015-08-18T00:36:00Z.

    MIN()

    Returns the smallest field values of the specified field keys.

    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]

    Description

    MIN(field_key): returns the smallest field value of the specified field key.

    MIN(/regular_expression/): returns the smallest field value of each field key that matches the specified regular expression.

    MIN(*): returns the smallest field value of each field key in the specified measurement.

    MIN(field_key),tag_key(s),field_key(s): returns the smallest field value of the specified field key, and the related tag and field (if applicable).

    MIN(): supports INT64 and FLOAT64 field values.

    Example 1: Obtain the smallest field value of the specified field key

    SELECT MIN("water_level") FROM "h2o_feet"

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

    This query returns the smallest field value of the water_level field key in the h2o_feet measurement.

    Example 2: Obtain the smallest field value of each field key in the specified measurement

    SELECT MIN(*) FROM "h2o_feet"

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

    This query returns the smallest field value of each field key that stores numeric values in the h2o_feet measurement. The h2o_feet measurement contains only the water_level field of the numeric data type.

    Example 3: Obtain the smallest field value of each field key that matches the specified regular expression

    SELECT MIN(/water/) FROM "h2o_feet"

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

    This query returns the smallest field value of each field key that contains water and stores numeric values in the h2o_feet measurement.

    Example 4: Obtain the smallest field value of the specified field key, and the related tag and field

    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

    This query returns the smallest field value of the water_level field key in the h2o_feet measurement. This query also returns the values of the location tag key and the level description field key.

    Example 5: Obtain the smallest field value of the specified field key for each time interval by using a query that includes 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:00Z9.01
2015-08-18T00:00:00Z8.005
2015-08-18T00:12:00Z7.762
2015-08-18T00:24:00Z7.5

    This query returns the smallest field value of the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 12-minute interval and the specified tag. For intervals during which no data is reported, the fill() function returns 9.01. The query limits the number of returned points to 4, and limits the number of returned series to 1.

    Note

    The GROUP BY time() clause overwrites the original timestamps of points. Each timestamp in the query results indicates the start time of a 12-minute interval. The time interval that includes the first point starts from 2015-08-17T23:48:00Z to 2015-08-18T00:00:00Z. The time interval that includes the last point starts from 2015-08-18T00:24:00Z to 2015-08-18T00:36:00Z.

    PERCENTILE()

    Returns the field value of 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]

    Description

    PERCENTILE(field_key,N): returns the field value of the Nth percentile for the specified field key.

    PERCENTILE(/regular_expression/,N): returns the field value of the Nth percentile for each field key that matches the specified regular expression.

    PERCENTILE(*,N): returns the field value of the Nth percentile for each field key in the specified measurement.

    PERCENTILE(field_key,N),tag_key(s),field_key(s): returns the field value of the Nth percentile for the specified field key, and the related tag and field (if applicable). The specified value for the N parameter must be an integer or a floating point number that ranges from 0 to 100. The PERCENTILE() function supports INT64 and FLOAT64 field values.

    Example 1: Obtain the field value of the fifth percentile for the specified field key

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

name: h2o_feet
time                   percentile
--------------
2015-08-31T03:42:00Z1.122

    This query returns the smallest field value that is larger than 5% of the field values for the water_level field key in the h2o_feet measurement.

    Example 2: Obtain the field value of the fifth percentile for each field key in the specified measurement

    SELECT PERCENTILE(*,5) FROM "h2o_feet"

name: h2o_feet
time                   percentile_water_level
--------------------------
2015-08-31T03:42:00Z1.122

    This query returns the smallest field value that is larger than 5% of the field values for the field key that stores numeric values in the h2o_feet measurement The h2o_feet measurement contains only the water_level field of the numeric type.

    Example 3: Obtain the field value of the fifth percentile for each field key that matches the specified regular expression

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

name: h2o_feet
time                   percentile_water_level
--------------------------
2015-08-31T03:42:00Z1.122

    This query returns the smallest field value that is larger than 5% of the field values for each specified field key in the h2o_feet measurement. The specified field key contains water and stores numeric values.

    Example 4: Obtain the field value of the fifth percentile for the specified field key, and the 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:00Z1.122       coyote_creek  below 3 feet

    This query returns the smallest field value that is larger than 5% of the field values for the water_level field key in the h2o_feet measurement. This query also returns the values of the location tag key and the level description field key.

    Example 5: Obtain the field value of the twentieth percentile for the specified field key in each time interval by using a query that includes 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:00Z15
2015-08-18T00:00:00Z2.064

    This query returns the smallest field value that is larger than 20% of the field values for the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-17T23:48:00Z to 2015-08-18T00:54:00Z. The query results are divided based on the specified 24-minute interval. For intervals in which no data is reported, the fill() function returns 15. This query limits the number of returned points to 2.

    Note

    The GROUP BY time() clause overwrites the original timestamps of points. Each timestamp in the query results indicates the start time of a 24-minute interval. The time interval that includes the first point starts from 2015-08-17T23:36:00Z to 2015-08-18T00:00:00Z. The time interval that includes the last point starts from 2015-08-18T00:00:00Z to 2015-08-18T00:24:00Z.

    FAQ

    What are the relationships between the PERCENTILE() function and other InfluxQL functions?

    • The PERCENTILE(<field_key>,100) function is equal to the MAX(<field_key>) function.

    • The PERCENTILE(<field_key>, 50) function is similar to MEDIAN(<field_key>) function except when the specified field key includes an even number of field values. In this case, the MEDIAN() function returns the average of the two middle field values.

    • The PERCENTILE(<field_key>,0) function is not equal to the MIN(<field_key>) function. The PERCENTILE(<field_key>,0) function returns null.

    SAMPLE()

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

    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]

    Description

    SAMPLE(field_key,N): returns N random field values of the specified field key.

    SAMPLE(/regular_expression/,N): returns N random field values of each field key that matches the specified regular expression.

    SAMPLE(*,N): returns N random field values of each field key in the specified measurement.

    SAMPLE(field_key,N),tag_key(s),field_key(s): returns N random field values of the specified field key enclosed in parentheses, and the related tag and field (if applicable). The value of the N parameter must be an integer. The SAMPLE() functions support field values of all data types.

    Example 1: Obtain random field values of the specified field key

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

name: h2o_feet
time                   sample
----------
2015-09-09T21:48:00Z5.659
2015-09-18T10:00:00Z6.939

    This query returns two random points for the water_level field key in the h2o_feet measurement.

    Example 2: Obtain random field values of each field key in the specified measurement

    SELECT SAMPLE(*,2) FROM "h2o_feet"

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

    This query returns two random points for each field key in the h2o_feet measurement. The h2o_feet measurement contains the level description and water_level field keys.

    Example 3: Obtain random field values of each field key that matches the specified 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:00Z7.854
2015-09-09T20:30:00Z7.32
2015-09-13T19:18:00Z   between 3 and 6 feet

    This query returns two random points for each field key that contains level in the h2o_feet measurement.

    Example 4: Obtain random field values of the specified field key, and the 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:00Z5.689   coyote_creek  between 3 and 6 feet
2015-09-08T15:48:00Z6.391   coyote_creek  between 6 and 9 feet

    This query returns two random points for the h2o_feet field key in the water_level measurement. This query also returns the values of the location tag key and the level description field key.

    Example 5: Obtain random field values of the specified field key for each time interval by using a query that includes 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:00Z2.028
2015-08-18T00:30:00Z2.051

    This query returns a random point for the water_level field key in the h2o_feet measurement for each time interval. The specified time range is from 2015-08-18T00:00:00Z to 2015-08-18T00:30:00Z. The query results are divided based on the specified 18-minute interval.

    Note

    The GROUP BY time() clause does not overwrite the original timestamps of points.

    FAQ

    What are the impacts if I specify SAMPLE() functions and GROUP BY time() clauses in queries?

    If you specify SAMPLE() functions and GROUP BY time() clauses in queries, the queries return the specified number (N) of points based on the time intervals that are specified by the GROUP BY time() clauses. For most of the queries that include GROUP BY time() clauses, each returned timestamp indicates the start time of each time interval that is specified by the GROUP BY time() clauses. However, for the queries that use SAMPLE() functions and GROUP BY time() clauses, the original timestamps of points are retained.

    Example: The following query returns two points based on the 18-minute interval that is specified by the GROUP BY time() clause. Note: The original timestamps of the points are returned. The original timestamps do not need to match the start time of the time interval that is specified by the GROUP BY time() clause. 

    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:00Z2.116|
2015-08-18T00:12:00Z2.028|<-------Randomly-selected points for the first time interval
--
                           __
2015-08-18T00:18:00Z2.126|
2015-08-18T00:30:00Z2.051|<-------Randomly-selected points for the second time interval
--

    TOP()

    Returns the largest N 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]

    Description

    TOP(field_key,N): returns the largest N values of the specified field key.

    TOP(field_key,tag_key(s),N): returns the largest field value of the field key for each of the N tag values of the specified tag key.

    TOP(field_key,N),tag_key(s),field_key(s): returns the largest N values of the specified field key enclosed in parentheses, and the related tag and field (if applicable).

    TOP(): supports INT64 and FLOAT64 field values.

    Note

    If two or more duplicate largest field values exist and relate to each other, the TOP() function returns the field value that is attached with the earliest timestamp. If a TOP() function is used in combination with an INTO clause, the TOP() function differs from other InfluxQL functions in terms of the returned timestamps.

    Example 1: Obtain the largest three field values of the specified field key

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

name: h2o_feet
time                   top
-------
2015-08-29T07:18:00Z9.957
2015-08-29T07:24:00Z9.964
2015-08-29T07:30:00Z9.954

    This query returns the largest three field values of the water_level field key in the h2o_feet measurement.

    Example 2: Obtain the largest field value of the field key for each of the two tag values

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

name: h2o_feet
time                   top     location
---------------
2015-08-29T03:54:00Z7.205   santa_monica
2015-08-29T07:24:00Z9.964   coyote_creek

    This query returns the largest field value of the water_level field key for each of the two tag values of the location tag key.

    Example 3: Obtain the largest four field values of the specified field key, and the 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:00Z9.957  coyote_creek  at or greater than 9 feet
2015-08-29T07:24:00Z9.964  coyote_creek  at or greater than 9 feet
2015-08-29T07:30:00Z9.954  coyote_creek  at or greater than 9 feet
2015-08-29T07:36:00Z9.941  coyote_creek  at or greater than 9 feet

    This query returns the largest four field values of the water_level field key. This query also returns the values of the location tag key and the level description field key.

    Example 4: Obtain the largest three values of the specified field key for each time interval by using a query that includes 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:00Z7.11   coyote_creek
2015-08-18T00:54:00Z6.982  coyote_creek
2015-08-18T00:54:00Z2.054  santa_monica
2015-08-18T00:24:00Z7.635  coyote_creek
2015-08-18T00:30:00Z7.5    coyote_creek
2015-08-18T00:36:00Z7.372  coyote_creek
2015-08-18T00:00:00Z8.12   coyote_creek
2015-08-18T00:06:00Z8.005  coyote_creek
2015-08-18T00:12:00Z7.887  coyote_creek

    This query returns the largest three values of the water_level field key for each 24-minute interval from 2015-08-18T00:00:00Z to 2015-08-18T00:54:00Z. The query results are sorted in descending order based on timestamps.

    Note

    The GROUP BY time() clause does not overwrite the original timestamps of points.

    FAQ

    What are the impacts if I specify a TOP() function and a GROUP BY time() clause in the same query?

    If you specify a TOP() function and a GROUP BY time() clause in the same query, this query returns the specified number of points based on the time interval that is specified by the GROUP BY time() clause. For most of the queries that include GROUP BY time() clauses, each returned timestamp indicates the start time of each time interval that is specified by the GROUP BY time() clauses. However, for the queries that use TOP() functions and GROUP BY time() clauses, the original timestamps of points are retained.

    Example: The following query returns two points based on the 18-minute interval that is specified by the GROUP BY time() clause. Note: The original timestamps of the points are returned. The original timestamps do not need to match the start time of the time interval that is specified by the GROUP BY time() clause. 

    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:00Z2.064|
2015-08-18T00:06:00Z2.116|<-------Greatest points for the first time interval
--
                           __
2015-08-18T00:18:00Z2.126|
2015-08-18T00:30:00Z2.051|<-------Greatest points for the second time interval
--

    What are the impacts if the number of tag values for the specified tag key is smaller than N specified in the TOP() function?

    The queries that use the SELECT TOP(<field_key>,<tag_key>,<N>) syntax return a less-than-expected number of points. A tag key may contain X tag values. In this case, if you specify the number of tag values asN in a query and X is smaller than N, the query returns X points.

    Example: The following query requests the largest field value of the water_level field key for each of the three tag values of the location tag key. The query returns two points because the location tag key contains only the santa_monica and coyote_creek tag values. 

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

name: h2o_feet
time                  top    location
---------------
2015-08-29T03:54:00Z7.205  santa_monica
2015-08-29T07:24:00Z9.964  coyote_creek

    What are the impacts if I specify an INTO clause and a TOP() function that includes tag keys in the same query?

    If you use INTO clauses and do not use GROUP BY tag clauses in queries, most of InfluxQL functions convert the tags in the raw data into the fields in the newly written data. This rule applies to the TOP() function except in scenarios where tag keys are specified in the TOP() function: TOP(field_key,tag_key(s),N). In these cases, the specified tags are retained in the newly written data.

    Examples: The first query in the following code block returns the largest field value of the water_level field key for each of the two tag values of the location tag key. The query results are written to the top_water_levels measurement. In the second query, TSDB for InfluxDB® retains the location tag in the top_water_levels measurement. 

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

name: result
time                 written
-----------
1970-01-01T00:00:00Z2

> SHOW TAG KEYS FROM "top_water_levels"

name: top_water_levels
tagKey
------
location