Wire up ORDER BY

[poy]

> select statusCode from SeriesName order by time desc limit 10
name: HttpRequestRate
---------------------
time                statusCode
2015-03-19T20:09:14.667313352Z  200
2015-03-19T20:09:14.667531825Z  200
2015-03-19T20:09:14.674292103Z  200
2015-03-19T20:09:14.674539615Z  200
2015-03-19T20:09:23.334009887Z  200
2015-03-19T20:09:23.334241444Z  200
2015-03-19T20:09:23.33466066Z   200
2015-03-19T20:09:38.333210425Z  200
2015-03-19T20:09:38.333536172Z  200
2015-03-19T20:09:38.334099221Z  200

> select statusCode from SeriesName order by time asc limit 10
name: HttpRequestRate
---------------------
time                statusCode
2015-03-19T20:09:14.667313352Z  200
2015-03-19T20:09:14.667531825Z  200
2015-03-19T20:09:14.674292103Z  200
2015-03-19T20:09:14.674539615Z  200
2015-03-19T20:09:23.334009887Z  200
2015-03-19T20:09:23.334241444Z  200
2015-03-19T20:09:23.33466066Z   200
2015-03-19T20:09:38.333210425Z  200
2015-03-19T20:09:38.333536172Z  200
2015-03-19T20:09:38.334099221Z  200

[cannium]

+1

[neonstalwart]

i hate to just +1 so here's a shell script. i hope this isn't overlooked for 0.9

echo "creating database"
curl -G -ipv4 http://localhost:8086/query --data-urlencode "q=CREATE DATABASE foo"

echo "inserting data"
curl -d "{\"database\" : \"foo\", \"points\": [{\"name\": \"cpu\", \"tags\": {\"region\":\"uswest\",\"host\": \"server01\"},\"fields\": {\"value\": 100}}]}" -H "Content-Type: application/json" -ipv4 http://localhost:8086/write

echo "inserting data"
curl -d "{\"database\" : \"foo\", \"points\": [{\"name\": \"cpu\", \"tags\": {\"region\":\"uswest\",\"host\": \"server01\"},\"fields\": {\"value\": 300}}]}" -H "Content-Type: application/json" -ipv4 http://localhost:8086/write

echo "inserting data"
curl -d "{\"database\" : \"foo\", \"points\": [{\"name\": \"cpu\", \"tags\": {\"region\":\"uswest\",\"host\": \"server01\"},\"fields\": {\"value\": 200}}]}" -H "Content-Type: application/json" -ipv4 http://localhost:8086/write

sleep 1

echo "querying data"
curl -G -ipv4 http://localhost:8086/query --data-urlencode "db=foo" --data-urlencode "q=select * from cpu order by value asc" --data-urlencode "pretty=true"

echo "querying data"
curl -G -ipv4 http://localhost:8086/query --data-urlencode "db=foo" --data-urlencode "q=select * from cpu order by value desc" --data-urlencode "pretty=true"

[neonstalwart]

@beckettsean could this get put into the 0.9.0 milestone?

[aviau]

Same issue here.

[CrazyJvm]

really wanna know how this issue going?

[pauldix]

This will have to wait for a refactoring we're about to do on the query engine. Pushed to 0.9.1

[krylovsk]

So what is the "get the latest value of measurement X" workaround until then?

[pauldix]

Unfortunately, there is no good way to get it until we have that set up. If you have a regular sampling interval and you know it, then you can limit the time scope of your query accordingly.

[aviau]

Why don't support this ?

It's in the roadmap. Don't worry.

[mei-rune]

It's in the roadmap. 9.2?

[zhulinhong]

Thanks for your reply, so when can we get that version? @aviau

[beckettsean]

@zhulinhong we do not publish release dates for features. The 0.9.2 release will be out on July 23rd and this issue is currently scheduled for that release, but that doesn't mean it will be in that version. This issue contains ALL relevant info about the schedule and progress.

[dswarbrick]

0.92rc1 has an odd sense of humour.

> select derivative(value) from "interface_rx" where host='host.example.com' and instance='eth0' and type='if_octets' and time < now() - 1h group by time(1m) order by asc;
ERR: only ORDER BY ASC supported at this time

[jacek213]

did it make it into 0.9.2?

[gunnaraasen]

Working ORDER BY did not make it into 0.9.2. The distributed query engine rewrite ended up taking the whole 0.9.2 development cycle to complete. Hooking up ORDER BY is a high priority for 0.9.3.

@dswarbrick That error will be not be in 0.9.2 final (see #3409).

[CrazyJvm]

seem will not make it into 0.9.3 still...... really need this feature. Any related PR or solution discussion?

[beckettsean]

@CrazyJvm this is the only relevant issue for ORDER BY. I am unaware of a working PR yet.

[bal2ag]

Without orderby desc, is there a simple way to get the latest point for a given series? Right now it seems like I have to select all the points in the series and take the last one in my application. Not difficult but it's a lot of extra reads against the DB. I can cut it down by filtering on time but this comes at the possible cost of accuracy/data correctness in my use case (I won't always known how far back the "latest" point will be).

[jacek213]

You can use last() function, if you don't need the timestamp.

[pauldix]

yeah, but the way the last function works would be very inefficient. Realistically, we need to have this done so people can get the most recent data point in a series efficiently.

[beckettsean]

@jwilder are you just wiring up ASC and DESC for ORDER BY time, or will you also add ORDER BY tag in this iteration?

[jwilder]

@beckettsean Just time.

[beckettsean]

Added #3954 for ORDER BY tag