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.

score 51 · Answer 1 · edited May 02 '18 at 12:18

51

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.

edited May 02 '18 at 12:18

Mark Amery

143,130
81
406
459

answered Mar 23 '16 at 16:59

kpalatzky

1,213
1
11
26

Do you have any explanation on what these unexpected values look like and why they show up? – Daniel F Dec 12 '17 at 04:20
4

-1 for [FUD](https://en.wikipedia.org/wiki/Fear,_uncertainty_and_doubt). If you want to assert that something is broken, provide a demonstration of the brokenness - not a vague and untestable assertion that *"it could be possible that you get unexpected values "*. – Mark Amery Feb 05 '18 at 11:32
Running InfluxDB 1.7.9, this query returns more than a single result for a table/measurement. Evidently this is only good for a series. – computercarguy Nov 18 '19 at 19:15
1

Just checked with "EXPLAIN" and using "LAST()" with "WHERE time > now() - 1h" seems better/faster then using "ORDER BY ... LIMIT" even with "GROUP BY" - the latter used all the shards. – Radoslaw Garbacz Nov 18 '19 at 19:25
1

So using FIRST() in InfluxDB version > 0.8 is fast? – rojikada Mar 11 '21 at 12:00

José Molina · Answer 2 · 2017-01-26T08:43:29.567

23

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.

edited Jan 26 '17 at 08:43

answered Apr 23 '15 at 16:23

José Molina

529
2
7

1

This will not give you the last value if it's longer than x ago. – FelixJongleur42 Sep 22 '20 at 11:26

Pi Home Server · Accepted Answer · 2015-04-09T08:35:04.090

6

In the API, have a look at first/last to select the first or last record of a column. You also have top/bottom to select more than one record

[edit] top/bottom seem to return higest/lowest values of the time frame

edited Apr 09 '15 at 08:35

answered Apr 09 '15 at 08:17

Pi Home Server

487
1
3
17

Where is this documented? I cannot find it at http://influxdb.com/docs/v0.9/query_language/querying_data.html or http://influxdb.com/docs/v0.8/api/query_language.html – dukebody Apr 09 '15 at 08:20
2

For 0.8 it's in the aggregate functions : http://influxdb.com/docs/v0.8/api/aggregate_functions.html#first/last sorry to not have provided the link before ! – Pi Home Server Apr 09 '15 at 08:28
1

I'm most interested in getting the times for the first/last values...any way to do that? – tslater Sep 17 '15 at 00:38

Bertrand P · Answer 4 · 2022-02-23T17:08:22.683

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.

edited Feb 23 '22 at 17:08

answered Jul 05 '21 at 11:43

Bertrand P

820
7
24

score 4 · Answer 5 · answered May 15 '19 at 03:26

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

score 2 · Answer 6 · edited May 24 '17 at 11:35

2

SELECT * FROM <SERIES> ORDER BY ASC LIMIT 1

edited May 24 '17 at 11:35

answered May 24 '17 at 11:24

IsaacE

305
2
10

1

DESC for last value. ASC with LIMIT 1 will return the first. – hacklikecrack Mar 13 '20 at 08:53
For long series, this is also much slower than selecting `WHERE time > now - ...`. – Dan Dascalescu Mar 21 '21 at 04:25

score 1 · Answer 7 · answered May 03 '18 at 10:45

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.

InfluxDB - Getting only last value in query

7 Answers7

Linked