Hello,
How can I select data with group by and order by? Like mysql for example.
SQL example:
SELECT “probe”,“lastvalue”,“lastvalue_raw” FROM “test_traffic” GROUP BY “probe” ORDER BY “time” ASC
The “probe” field is string. Is this possible or not?
regards
Yes.
In fact the exact syntax you have quoted works fine for me.
Do you get an error? If so, what is it?
Antony.
The group by is not working correct. He should group by “probe” and the grouped sort by time.
The group by is not working correct. He should group by “probe” and the
grouped sort by time.
Here’s what I get for an almost identical query (obviously, I don’t have the
same data as you):
select callid from mqtt_consumer where time>now()-100m group by “server”
order by “time” asc limit 5
name: mqtt_consumer
tags: server=copper
time callid
1557745074984701258 20
1557745075984896607 20
1557745076986885320 20
1557745077987065206 20
1557745078987666812 20
name: mqtt_consumer
tags: server=dummy
time callid
1557743476450718138 0
1557743487627073587 0
1557743956638171048 0
1557743967739584863 0
1557744437052067885 0
name: mqtt_consumer
tags: server=iodine
time callid
1557746518841453207 5
1557746519834996465 5
1557746520835677473 5
1557746521840783283 5
1557746522836542594 5
So, I get three sets of results, each set grouped according to “server” and
each group sorted in order of “time”.
What are you getting and what are you expecting instead?
Antony.
Ok now is working. Not before, I dont know why. But thanks for your help.
I have another question regarding the auto generated field (from Telegraf) “time”. (Example value by me: 1557518403000000000) Can i change the format? Maybe via Telegraf.conf?
regards
I think i need this format : 2015-09-18T21:36:00Z. I’ll grap this via php. I need a time period.
Like this: SELECT “water_level” FROM “h2o_feet” WHERE time > ‘2015-09-18T21:24:00Z’ AND time < ‘2015-09-20T21:24:00Z’
Hi me again. The time issue is solved. Simply precision rfc3339. That it.
But the group by string field is not working.
Sql statement:
SELECT probe,lastvalue,lastvalue_raw FROM data_traffic WHERE time >= ‘2019-05-13T00:00:00Z’ AND time <= ‘2019-05-14T23:59:00Z’ GROUP BY probe ORDER BY time ASC
or simply
SELECT probe,lastvalue,lastvalue_raw FROM prtg_data_traffic GROUP BY probe ORDER BY time ASC
Database example data:
time probe lastvalue lastvalue_raw
2019-05-13T20:00:03Z Test 1 1.147 GB 1147.2643
2019-05-13T20:00:03Z Test 2 1.158 GB 1158.1432
2019-05-13T20:00:03Z Test 3 1.161 GB 1160.6873
2019-05-14T08:00:03Z Test 1 899 GB 899.2385
2019-05-14T08:00:03Z Test 2 899 GB 898.7219
2019-05-14T08:00:03Z Test 3 919 GB 919.0146
2019-05-14T08:00:03Z Test 4 931 GB 930.6706
2019-05-14T08:00:03Z Test 5 935 GB 934.9691
The output is not in blocks grouped by probe and then ordered by time. I cant use order by time, probe.
regards
I believe you’re getting this data into Influx via telegraf.
Do you have:
tag_keys = [“probe”]
in your /etc/telegraf/telegraf.conf?
Antony.
Hey!
No. Only the identifier/index
tag_keys = [“objid”]
Ok! Now its working. My mistake. Thanks for your help.
group by objid
