Influxdb query last row of all series in a measurement

InfluxDB 1 Kapacitor
,
1

I trying to find a way to get the most recent row for each serie in a measurement.

for example:

Assuming the series in results measurement are:

select series from test_result
results,service=MyService,team=A
result,service=MyService,team=B
result,service=MyService,team=C

and the rows in a given time frame are:

select * from test_result order by time desc

time service team status duration

1523370939000000000 MyService A 1 300
1523370940000000000 MyService B 1 300
1523370941000000000 MyService A 1 300
1523370941000000000 MyService C 1 300
1523371748000000000 MyService A 1 300
1523371749000000000 MyService B 1 300
1523371750000000000 MyService B 1 300
1523371754000000000 MyService A 1 300

I would expect the query to return the first, second and fourth rows.
Any suggestion is much appreciated.

Thanks!

2

To separate the series, you can add GROUP BY *, which will give you the results separated by series. Then you can add aggregates to your query, like LAST.
For example:
SELCT LAST(field_name, *) from test_result GROUP BY *

Keep in mind that your fields are also a factor here. You can use * without specifying a field, but there’s room for error there. It’s better to specify a field if you know what you need.

3

That did the job. Thanks!

found out that last() accepts only one input so here’s the correct syntax if anyone ever need it:

SELECT LAST(field_name), * from test_result GROUP BY *

1 Like
4

Yep, thanks for fixing that!

5

Hi katy,
I need to ask you something related to your answer please. I cannot get the latest data per series and per day. Could you help please?
My following code miss some data.

SELECT “account_entity” as app_vendor_id, last(“balance”), “source”, “base_ccy” as currency
FROM “data_feeds”
GROUP BY *, time(1d)
ORDER BY “time” DESC

6

@Mahfoud_Delal what is wrong with the results you are getting?