[feature request] query to return N most recent points in a measurement, with fields

[contentfree]

Just upgraded today. I have a measurement with ~10k records. The expectation, obviously, that select * from my_measurement limit 1 would return one record. However, it seems to return (at least) one record per series in a measurement. This was surprising.

[desa]

@contentfree what do you mean by '(at least) one record per series'. When I run the query you've specified I only get one record per series.

[gunnaraasen]

select * from my_measurement

is equivalent to

select [all fields] from my_measurement group by [all tags]

Does writing out the fields give you what you expect?

select field1, field2, field3 from my_measurement limit 1

[beckettsean]

LIMIT 1 returns one point from every matching series. SLIMIT 1 returns all points from the first matching series.

@gunnaraasen is correct, SELECT field_key FROM measurement LIMIT 1 returns only one point, regardless of how many series match. Be aware that it is not deterministic which series will match first.

Docs could be clearer on this. Opened https://github.com/influxdb/influxdb.com/issues/73 to fix it.

[contentfree]

That's very confusing and unexpected. Why is select * from my_measurement equivalent to select [all fields] from my_measurement group by [all tags] and not select [all fields] from my_measurement as one would expect (given the choice of a SQL lookalike language)?

If I have tagged measurements by host, for example, and I do this for hundreds of hosts, when I ask for 1 item without providing a tag then I'd be very surprised to get hundreds of records back. It should not group by anything unless I tell it to group by something, no?

So how do I get all fields from a measurement without writing out each field and without it group by all tags? How do I get the most recent n records from a measurement?

[beckettsean]

@contentfree the implicit GROUP BY * is a way of returning the tag values when doing a SELECT *. See #1997 for more details. I agree it can lead to non-intuitive behavior, but the prior behavior select [all fields] from my_measurement didn't return any tags and that appeared to be a more non-intuitive issue for the community.

How do I get the most recent n records from a measurement?

I don't think there is a way to do this currently. It's a reasonable feature request.

[beckettsean]

Clarifying: SELECT * FROM my_measurement ORDER BY DESC LIMIT N returns N points from each series in my_measurement. SELECT * FROM my_measurement ORDER BY DESC SLIMIT N returns the full series from the first N matching series.

This new function would return the N first matching points in the measurement, regardless of series.

[beckettsean]

SELECT * FROM measurement ORDER BY time DESC LIMIT 10 will return the 10 most recent points in the measurement, regardless of series.