How to retrieve last record for each field key from measurement with corresponding timestamp

InfluxDB 1 InfluxQL
1

Hello everybody,
I’m learning influxdb now. If you could help me with below question that would be greatly appreciated.

Influx db: 1.8

measurement: codes
tagKey

status

fieldKey fieldType

M100 string
M200 string
M300 string
M300 string
M400 string
(Note: Number of field keys are in the measurement “codes” could any number.)

codes measurments
codes measurments928×389 7.16 KB

data being inserted in “codes” measurement for each field. Data could be inserted into measurement for one or more field keys every 1 Hz. So, there may be multiple records for some field keys and probably one or zero records inserted for other field keys.

I wanted to query last inserted record for each field key with corresponding time stamps (Please refer to expected output above.).

I tried, SELECT DISTINCT(*) FROM “codes” ORDER BY time DESC

based on influxdb 1.8 documentation SELECT DISTINCT(*) FROM “codes” suppose to work. But, query returns error expected field key inside DISTINCT parenthesis.

last(*) won’t work for me because timestamp is condensed to one.

Appreciate your help.

Thanks.
Eddy

2

Hello @eddy,
Have you tried using limit instead?

3

Hi
Thank for the quick response.

LIMIT would give me latest record for all fieldKeys.

I wanted last entry for each fieldKeys with corresponding timestamp.

influx_query
influx_query928×389 9.34 KB

Please correct me if I assume different.

4

Hello @eddy,
I apologize I misunderstood your post. Thank you for clarifying
Unfortunately you can’t group by field with influxql so you can achieve this with limit.
However I think you can do something like:

What error does distinct give?

You can use multiple last statements:

SELECT last("M100"), last("M200"), last("M300"), last("M400"), last("M500") from ....