InfluxDB - Getting only last value in query

Question

Is possible to query only for the last value (or n-th value) of the results of a query?

For example, in the query:

SELECT value FROM response_times WHERE time > now() - 1h limit 1000;

Is possible to get only the last value, i.e. the one more far ago in time (possibly the 1000-th element)?

Of course I can retrieve them all and then skip to the last one, but I don't want to waste bandwith this way.

Answer 1

If you are using InfluxDB 0.8 dont use FIRST() or LAST() if you have not GROUP BY because its very slow :(

So if you want to get the these Values you shoud use:

First Value:

SELECT * FROM <SERIES> GROUP BY * ORDER BY ASC LIMIT 1

Last Value:

SELECT * FROM <SERIES> GROUP BY * ORDER BY DESC LIMIT 1

Don't delete the GROUP BY * because then it could be possible that you get unexpected values then.

Answer 2

SELECT last(value) FROM response_times WHERE time > now() - 1h;

That should return the last value of the column.

However, if you want you can split up the sequence in smaller pieces and select the last value. For instance:

 SELECT last(value) FROM response_times WHERE time > now() - 1h GROUP BY time(60s);

It will split up the sequence in 60-second fragments and will pick up for each fragment the latest value.

Answer 3

here is a concrete sample, suppose we have data:

> select *,tag from measurement
name: measurement
time                 field_1    tag_1
----                 -------    ------
2019-05-15T03:07:52Z 100      A
2019-05-15T03:07:52Z 101      B
2019-05-15T03:09:52Z 100      A
2019-05-15T03:09:52Z 101      B

and you would like to take the last two columns(latest records per tag_1), then you can do this:

> select last(field_1),* from measurement group by *
name: measurement
tags: tag_1=A
time                 last     field_1 
----                 ----    ------- 
2019-05-15T03:09:52Z 100        100     

name: measurement
tags: tag_1=B
time                 last  field_1 
----                 ----  ------- 
2019-05-15T03:09:52Z 101    101 

and also the visualization on grafana, it'll always show the last records per tag

Answer 4

SELECT * FROM <SERIES> ORDER BY ASC LIMIT 1

Answer 5

There is no bug or any issue about LIMIT. Yes there is one, but it's about SLIMIT (Series Limit). LIMIT can be safely used for getting first n records.

So in short, the syntax for LIMIT:

SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] LIMIT <N>

the syntax for SLIMIT:

SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] GROUP BY *[,time(<time_interval>)] [ORDER_BY_clause] SLIMIT <N>

You see that GROUP BY clause is [ optional ] in LIMIT.

For detailed explanation about usages of LIMIT and SLIMIT, here is version 1.5 document. And here is the ongoing issue about SLIMIT.

Answer 6

If like me you are looking for the same feature but with the Flux DSL (influx 2).

You can use the tail function https://docs.influxdata.com/influxdb/v2.0/reference/flux/stdlib/built-in/transformations/tail/

(...)
  |> tail(n: 1)

The last function didn't work out for me.