All Products
Search
Document Center

Select Statement

Last Updated: Aug 05, 2020

You can execute the SELECT statement to query data from one or more measurements.

Syntax

  1. SELECT <field_key>[,<field_key>,<tag_key>] FROM <measurement_name>[,<measurement_name>]

Description

The SELECT statement requires a SELECT clause and a FROM clause.

SELECT clause

You can use the following formats to specify data in the SELECT clause:

  • SELECT * returns all the fields and tags.
  • SELECT "<field_key>" returns a specific field.
  • SELECT "<field_key>","<field_key>" returns multiple fields.
  • SELECT" <field_key>","<tag_key>" returns a specific field and a specific tag. If the SELECT clause contains a tag, you must specify at least one field in the SELECT clause.
  • SELECT" <field_key>"::field,"<tag_key>"::tag returns a specific field and a specific tag. The ::[field | tag] syntax specifies the type of the identifier. You can use this syntax to differentiate between field keys and tag keys that have the same name.

The SELECT clause also supports arithmetic operations, functions, cast operations, and regular expressions.

FROM clause

You can use the following formats to specify measurements in the FROM clause:

FROM <measurement_name>returns data from a single measurement. If you use the command-line interface (CLI), TSDB for InfluxDB® queries the measurement that uses the DEFAULT retention policy in the database specified by the USE statement. If you use the HTTP API, TSDB for InfluxDB® queries the measurement that uses the DEFAULT retention policy in the database specified by the db parameter.

FROM <measurement_name>,<measurement_name>returns data from multiple measurements.

FROM <database_name>.<retention_policy_name>.<measurement_name>returns data from a fully qualified measurement. To fully qualify a measurement, specify its database and retention policy.

FROM <database_name>..<measurement_name>returns data from a measurement that uses the DEFAULT retention policy in the specified database.

The FROM clause also supports regular expressions.

Quoting

If identifiers contain characters other than [A-z, 0-9, and underscores (_)], or begin with a digit, or are InfluxQL keywords, the identifiers must be enclosed in double quotation marks (“). In other scenarios, you do not need to use double quotation marks (“) to enclose identifiers. However, we recommend that you use double quotation marks (“) to enclose identifiers.

Notes: The quoting syntax for queries differs from that for the line protocol. For more information, view the rules for using single quotation marks (‘) and quotation marks (“) in queries.

Examples

Query all the fields and tags from a single measurement

  1. > SELECT * FROM "h2o_feet"
  2. name: h2o_feet
  3. --------------
  4. time level description location water_level
  5. 2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
  6. 2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
  7. [...]
  8. 2015-09-18T21:36:00Z between 3 and 6 feet santa_monica 5.066
  9. 2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938

The query returns all the fields and tags from the h2o_feet measurement.

If you use the CLI, enter USE NOAA_water_database before you run the preceding query. In this case, TSDB for InfluxDB® queries the data that uses the DEFAULT retention policy in the database specified by the USE statement. If you use the HTTP API, set the db parameter to NOAA_water_database. If you do not set the rp parameter, the HTTP API automatically queries the data that uses the DEFAULT retention policy in the NOAA_water_database database.

Query specific tags and fields from a single measurement

  1. > SELECT "level description","location","water_level" FROM "h2o_feet"
  2. name: h2o_feet
  3. --------------
  4. time level description location water_level
  5. 2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
  6. 2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
  7. [...]
  8. 2015-09-18T21:36:00Z between 3 and 6 feet santa_monica 5.066
  9. 2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938

The query returns the values of the level description and water_level fields and the location tag. Note that you must specify at least one field in the SELECT clause that contains a tag.

Query specific tags and fields from a single measurement by using a query where identifier types are specified

  1. > SELECT "level description"::field,"location"::tag,"water_level"::field FROM "h2o_feet"
  2. name: h2o_feet
  3. --------------
  4. time level description location water_level
  5. 2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
  6. 2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
  7. [...]
  8. 2015-09-18T21:36:00Z between 3 and 6 feet santa_monica 5.066
  9. 2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938

The query returns the values of the level description and water_level fields and the location tag. The ::[field | tag] syntax specifies whether the identifier is a field or a tag. Use ::[field | tag] to differentiate between field keys and tag keys that have the same name. This syntax is not required in most cases.

Query all the fields from a single measurement

  1. > SELECT *::field FROM "h2o_feet"
  2. name: h2o_feet
  3. --------------
  4. time level description water_level
  5. 2015-08-18T00:00:00Z below 3 feet 2.064
  6. 2015-08-18T00:00:00Z between 6 and 9 feet 8.12
  7. [...]
  8. 2015-09-18T21:36:00Z between 3 and 6 feet 5.066
  9. 2015-09-18T21:42:00Z between 3 and 6 feet 4.938

The query returns all the fields from the h2o_feet measurement. You can combine the * syntax and the :: syntax in the SELECT clause.

Query a specific field from a single measurement and perform basic arithmetic operations

  1. > SELECT ("water_level" * 2) + 4 from "h2o_feet"
  2. name: h2o_feet
  3. --------------
  4. time water_level
  5. 2015-08-18T00:00:00Z 20.24
  6. 2015-08-18T00:00:00Z 8.128
  7. [...]
  8. 2015-09-18T21:36:00Z 14.132
  9. 2015-09-18T21:42:00Z 13.876

The query multiplies the values of the water_level field by two and adds four to those values. Note that TSDB for InfluxDB® follows the standard order of arithmetic operations. For more information, see the “InfluxQL mathematical operators” topic.

Query all data from multiple measurements

  1. > SELECT * FROM "h2o_feet","h2o_pH"
  2. name: h2o_feet
  3. --------------
  4. time level description location pH water_level
  5. 2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
  6. 2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
  7. [...]
  8. 2015-09-18T21:36:00Z between 3 and 6 feet santa_monica 5.066
  9. 2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938
  10. name: h2o_pH
  11. ------------
  12. time level description location pH water_level
  13. 2015-08-18T00:00:00Z santa_monica 6
  14. 2015-08-18T00:00:00Z coyote_creek 7
  15. [...]
  16. 2015-09-18T21:36:00Z santa_monica 8
  17. 2015-09-18T21:42:00Z santa_monica 7

The query returns all the fields and tags from two measurements: h2o_feet and h2o_pH. Separate measurements with commas (,).

Query all data from a fully qualified measurement

  1. > SELECT * FROM "NOAA_water_database"."autogen"."h2o_feet"
  2. name: h2o_feet
  3. --------------
  4. time level description location water_level
  5. 2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
  6. 2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
  7. [...]
  8. 2015-09-18T21:36:00Z between 3 and 6 feet santa_monica 5.066
  9. 2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938

The query returns data from the h2o_feet measurement that uses the autogen retention policy in the NOAA_water_database database.``

If you use the CLI, you can fully qualify a measurement to specify a database and a retention policy that is not the DEFAULT retention policy. In this scenario, you do not need to execute the USE statement to specify a database. If you use the HTTP API, you can fully qualify a measurement to specify a database and a retention policy. In this scenario, you do not need to specify the db and rp parameters in the HTTP API request.

Query all the data from a measurement in a specified database

  1. > SELECT * FROM "NOAA_water_database".."h2o_feet"
  2. name: h2o_feet
  3. --------------
  4. time level description location water_level
  5. 2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
  6. 2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
  7. [...]
  8. 2015-09-18T21:36:00Z between 3 and 6 feet santa_monica 5.066
  9. 2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938

The query returns all data from the h2o_feet measurement that uses the DEFAULT retention policy in the NOAA_water_database database.`` indicates that the DEFAULT retention policy is used for the specified database.

If you use the CLI, you can use this method to specify a database instead of executing the USE statement to specify a database. If you use the HTTP API, you can use this method to specify a database instead of specifying the db parameter.

FAQ about the SELECT statement

Why does a query return no data when I query tag keys by using the SELECT clause?

A query returns data only if the SELECT clause in the query contains at least one field key. If the SELECT clause in a query includes only one or more tag keys, the query returns no data. This occurs because of the way how the system stores data.

Example

The following query returns no data because the SELECT clause includes only the location tag key and no field keys.

  1. > SELECT "location" FROM "h2o_feet"
  2. >

To return the data associated with the location tag key, the SELECT clause in the query must include at least one field key, such as water_level.

  1. > SELECT "water_level","location" FROM "h2o_feet" LIMIT 3
  2. name: h2o_feet
  3. time water_level location
  4. ---- ----------- --------
  5. 2015-08-18T00:00:00Z 8.12 coyote_creek
  6. 2015-08-18T00:00:00Z 2.064 santa_monica
  7. [...]
  8. 2015-09-18T21:36:00Z 5.066 santa_monica
  9. 2015-09-18T21:42:00Z 4.938 santa_monica


InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.