Given below measurement in InfluxDB, I want to display a table in Grafana with a Top 2 results consisting of most frequent occurring device for a given sensor. So expected result for the below data would be a table with 2 tows of
device total ------ ---- 345678 7 234567 3
> select * from incoming_events_by_device limit 50; name: incoming_events_by_device time device host metric_type sensor value ---- ------ ---- ----------- ------ ----- 1535575550000000000 123456 foo.com counter efg 1 1535575550000000000 123456 foo.com counter efg 1 1535575550000000000 234567 foo.com counter efg 1 1535575550000000000 234567 foo.com counter hij 2 1535575550000000000 234567 foo.com counter efg 1 1535575550000000000 345678 foo.com counter nice 1 1535575550000000000 358168 foo.com counter nice 1 1535575550000000000 345678 foo.com counter nice 1 1535575550000000000 345678 foo.com counter nice 4 1535575550000000000 345678 foo.com counter efg 1 1535575550000000000 345678 foo.com counter efg 12
I have tried using sub query like this but not getting the expected result. It shows empty response.
SELECT top(incoming_devices_count,5) FROM (SELECT sum(device) as incoming_devices_count FROM incoming_events_by_device WHERE sensor='nice' AND time > now() - 30m group by device)
Found the query.
select top(total, 3), device from (select sum(value) as total from incoming_events_by_device where sensor='nice' AND time > now() - 30m group by device);