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

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.)

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

Hello @eddy,
Have you tried using limit instead?
https://docs.influxdata.com/influxdb/v1.7/query_language/data_exploration/#the-limit-and-slimit-clauses

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.

Please correct me if I assume different.

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 ....