Query the latest values
The LATEST function returns the most recent N values for a specified field column, per time series. Use it to display current device status, retrieve the last known sensor reading, or build real-time monitoring dashboards.
Applicable engines and versions
The latest value query is applicable only to the time-series engine, with no version restriction.
Syntax
select_latest_by_statement ::= SELECT ( select_clause )
FROM table_identifier
WHERE where_clause
SAMPLE BY 0
select_clause ::= selector [ AS identifier ] ( ',' selector [ AS identifier ] )
selector ::= tag_identifier | time | latest '(' field_name [ ',' N ] ')'
where_clause ::= relation ( AND relation )* (OR relation)*
relation ::= ( field_identifier | tag_identifier ) operator term
operator ::= '=' | '<' | '>' | '<=' | '>=' | '!=' | IN The latest value query cannot be used directly with offset, limit, or group by field clauses. However, you can use them together through subqueries.
Parameters
selector
You can set tag columns (tag_identifier), the timestamp column (time), and the latest-value function expression as selectors.
Latest-value function expression (latest '(' field_name [ ',' N] ')')
Only the LATEST function is supported, and the LATEST function cannot be combined with other functions such as RATE, DELTA, MAX, or MIN. The parameters of the LATEST function are described as follows:
Parameters
Parameter | Description |
| The name of the field column. |
| An integer specifying the number of most recent values to return. Default: 1. |
Examples
Assume that the sample table sensor has the following structure:
-- Create table
CREATE TABLE sensor (
device_id VARCHAR TAG,
region VARCHAR TAG,
time TIMESTAMP,
temperature DOUBLE,
humidity DOUBLE,
PRIMARY KEY(device_id));
-- Write data
INSERT INTO sensor (device_id, region, time, temperature, humidity) VALUES
('F07A1260','north-cn','2021-04-22 15:33:00',12.1,45),
('F07A1260','north-cn','2021-04-22 15:33:10',13.2,47),
('F07A1260','north-cn','2021-04-22 15:33:20',10.6,null),
('F07A1260','north-cn','2021-04-22 15:33:30',null,48),
('F07A1260','north-cn','2021-04-22 15:33:20',10.6,46),
('F07A1261','south-cn','2021-04-22 15:33:00',18.1,44),
('F07A1261','south-cn','2021-04-22 15:33:10',19.7,44);A null value in the insert statement means no data was recorded for that field at that timestamp.
Query the latest value
Query the latest temperature data of devices F07A1260 and F07A1261.
SELECT device_id,region,time,latest(temperature) AS temperature FROM sensor WHERE device_id in ('F07A1260', 'F07A1261') SAMPLE BY 0;The result:
+-----------+----------+---------------------------+-------------+
| device_id | region | time | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1261 | south-cn | 2021-04-22T15:33:10+08:00 | 19.7 |
| F07A1260 | north-cn | 2021-04-22T15:33:20+08:00 | 10.6 |
+-----------+----------+---------------------------+-------------+Query the latest N values
Query the latest two temperature records of devices F07A1260 and F07A1261.
SELECT device_id,region,time,latest(temperature, 2) AS temperature FROM sensor WHERE device_id in ('F07A1260', 'F07A1261') SAMPLE BY 0;The result:
+-----------+----------+---------------------------+-------------+
| device_id | region | time | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1261 | south-cn | 2021-04-22T15:33:10+08:00 | 19.7 |
| F07A1261 | south-cn | 2021-04-22T15:33:00+08:00 | 18.1 |
| F07A1260 | north-cn | 2021-04-22T15:33:20+08:00 | 10.6 |
| F07A1260 | north-cn | 2021-04-22T15:33:10+08:00 | 13.2 |
+-----------+----------+---------------------------+-------------+Query the latest value across multiple columns
Query the latest values of multiple columns of device F07A1260.
SELECT device_id,region,time,latest(temperature) AS temperature, latest(humidity) AS humidity FROM sensor WHERE device_id in ('F07A1260') SAMPLE BY 0;The result:
+-----------+----------+---------------------------+-------------+----------+
| device_id | region | time | temperature | humidity |
+-----------+----------+---------------------------+-------------+----------+
| F07A1260 | north-cn | 2021-04-22T15:33:30+08:00 | null | 48 |
| F07A1260 | north-cn | 2021-04-22T15:33:20+08:00 | 10.6 | null |
+-----------+----------+---------------------------+-------------+----------+Each column's latest value is resolved independently. When the latest values for different columns fall on different rows, the result includes both rows with null filling the missing values.
Nested query
Combined with the GROUP BY clause, query the maximum values of the latest two temperature records of devices F07A1260 and F07A1261 respectively.
SELECT device_id, max(temperature) AS max_temperature FROM (SELECT device_id, region,time, latest(temperature,2) AS temperature FROM sensor SAMPLE BY 0) GROUP BY device_id;The result:
+-----------+-----------------+
| device_id | max_temperature |
+-----------+-----------------+
| F07A1261 | 19.7 |
| F07A1260 | 13.2 |
+-----------+-----------------+Pagination query
Query the latest two temperature records of devices F07A1260 and F07A1261. Set LIMIT (the number of returned rows) and OFFSET (the offset) to 1 to return the second record in the query result.
SELECT device_id, region, temperature FROM (SELECT device_id, region, time, latest(temperature,2) AS temperature FROM sensor SAMPLE BY 0) LIMIT 1 OFFSET 1;The result:
+-----------+----------+-------------+
| device_id | region | temperature |
+-----------+----------+-------------+
| F07A1261 | south-cn | 18.1 |
+-----------+----------+-------------+