Query Latest Time Series Values Efficiently with LATEST - Lindorm

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

LindormTSDB only (all versions)

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

Every latest-value query ends with SAMPLE BY 0. The LATEST function is the only function supported in the selector expression — it cannot be combined with RATE, DELTA, MAX, or MIN.

Parameters

Parameter	Description
`field_name`	The name of the field column to query.
`N`	An integer specifying the number of latest values to return per series. Defaults to `1`.

Constraints

Latest-value queries cannot be used directly with OFFSET, LIMIT, or GROUP BY. Wrap the latest-value query as a subquery to use these clauses:

-- Correct: wrap the latest-value query as a subquery
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;

Examples

The following examples use a sensor table created with this schema:

-- Create the table.
CREATE TABLE sensor (
    device_id VARCHAR TAG,
    region VARCHAR TAG,
    time   TIMESTAMP,
    temperature DOUBLE,
    humidity DOUBLE,
    PRIMARY KEY(device_id));

-- Insert data into the table.
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);

Note

A null value in the insert statement means no data was recorded for that field at that timestamp.

Query the latest value

Retrieve the most recent temperature reading for devices F07A1260 and F07A1261:

SELECT device_id, region, time, latest(temperature) AS temperature
FROM sensor
WHERE device_id IN ('F07A1260', 'F07A1261')
SAMPLE BY 0;

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

Retrieve the two most recent temperature readings for each device:

SELECT device_id, region, time, latest(temperature, 2) AS temperature
FROM sensor
WHERE device_id IN ('F07A1260', 'F07A1261')
SAMPLE BY 0;

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

Retrieve the latest value of both temperature and humidity for 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;

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     |
+-----------+----------+---------------------------+-------------+----------+

Note

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.

Use GROUP BY with a subquery

Find the highest of the two most recent temperature readings for each device:

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;

Result:

+-----------+-----------------+
| device_id | max_temperature |
+-----------+-----------------+
| F07A1261  | 19.7            |
| F07A1260  | 13.2            |
+-----------+-----------------+

Paginate latest-value results

Use LIMIT and OFFSET in an outer query to paginate over latest-value results. The following example retrieves the second row from the latest two temperature readings across both devices:

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;

Result:

+-----------+----------+-------------+
| device_id |  region  | temperature |
+-----------+----------+-------------+
| F07A1261  | south-cn | 18.1        |
+-----------+----------+-------------+