Select truly last (= newest timestamp) value from a database

Fluxlang
1

Hello all,

for some time I have used this query to get the last value from a measurement:

from(bucket:"iobroker/global")
  |> range(start:-8d)
  |> filter(fn:(r) => r._measurement == "gaszaehler.0.absolute")
  |> last()

However, this doesn’t work if the last value (or any others) have tags. Then I get a result for each combination of tags.
But I want to ignore all tags and just get the newest entry in the measurement, like this in SQL:

SELECT timestamp, value FROM measurement ORDER BY timestamp DESC LIMIT 1

Thanks!

2

hello @Jens,
Try applying a group() first like:

from(bucket:"iobroker/global")
  |> range(start:-8d)
  |> filter(fn:(r) => r._measurement == "gaszaehler.0.absolute")
  |> group()
  |> last()
3

Hello,
thank you! This works, but only if I don’t have measurements with tags in the specified time range. If there are any with tags, these will be preferred by the last() call and any later ones without tags will be ignored.
Note that I don’t want to specifially exclude tags, I just want to ignore them when getting the newest entry in the database.

I also tried using keep(columns: ["_value"]) as an additional filter, but this also doesn’t help.
How can I simply ignore all tags when requesting data?

4

No solution to this?

5

@Jens last() doesn’t necessarily return the last value based on time. It returns the last row of each input table based on the row sorting. By default, rows are sorted by time, however, one thing about group() is that it doesn’t guarantee row sorting, so you may need to resort by time after group():

from(bucket:"iobroker/global")
    |> range(start:-8d)
    |> filter(fn:(r) => r._measurement == "gaszaehler.0.absolute")
    |> group()
    |> sort(columns: ["_time"])
    |> last()
2 Likes