first.md

api_name	excerpt	topics	keywords	api	hyperfunction	products
first()	Get the first value in one column when rows are ordered by another column	hyperfunctions	hyperfunctions	license type version apache function stable 0.0.11-beta	type one-step aggregate	cloud mst self_hosted

first()

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

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.

Samples

Get the earliest temperature by device_id:

SELECT device_id, first(temp, time)
FROM metrics
GROUP BY device_id;

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

SELECT
   TIME_BUCKET('5 MIN', time_column) AS interv,
   AVG(temperature) as avg_temp,
   first(temperature,time_column) FILTER(WHERE time_column IS NOT NULL) AS beg_temp,
   last(temperature,time_column) FILTER(WHERE time_column IS NOT NULL) AS end_temp
FROM sensors
GROUP BY interv

Required arguments

Name	Type	Description
value	TEXT	The value to return
time	TIMESTAMP or INTEGER	The timestamp to use for comparison