last()

The last aggregate allows you to get the value of one column as ordered by another. For example, last(temperature, time) returns the latest temperature value based on time within an aggregate group.

important

The last and first commands do not use indexes, they perform a sequential scan through the group. They are primarily used for ordered selection within a GROUP BY aggregate, and not as an alternative to an ORDER BY time DESC LIMIT 1 clause to find the latest value, which uses indexes.

Required arguments

Sample usage

Get the temperature every 5 minutes for each device over the past day:

2. SELECT device_id, time_bucket('5 minutes', time) AS interval,

4. last(temp, time)

6. FROM metrics

8. WHERE time > now () - INTERVAL '1 day'

10. GROUP BY device_id, interval

12. ORDER BY interval DESC;

This example uses first and last with an aggregate filter, and avoids null values in the output:

2. SELECT

4. TIME_BUCKET('5 MIN', time_column) AS interv,

6. AVG(temperature) as avg_temp,

8. first(temperature,time_column) FILTER(WHERE time_column IS NOT NULL) AS beg_temp,

10. last(temperature,time_column) FILTER(WHERE time_column IS NOT NULL) AS end_temp

12. FROM sensors

14. GROUP BY interv