CODEWITHSUNDEEP
questdb
djbobo
djbobo

Reputation: 547

How do I get the top-5 devices by value from a table of IoT sensors in QuestDB?

I have a table that looks like the following:

prob name freq lim count hash time
0.0755 ard 0.988 0.633 0 5YIF6HSOtHN9HdcE5IPzMe 2021-04-09T10:56:00.986441Z
0.0755 ard 0.988 0.633 0 5YIF6HSOtHN9HdcE5IPzMe 2021-04-09T10:56:00.986441Z
0.0006259999999999999 srd2 0.987 0.649 0 5GFwJHucflHj46Hwet6qvc 2021-04-09T10:56:14.799226Z
0.0006259999999999999 ard2 0.987 0.649 0 5GFwJHucflHj46Hwet6qvc 2021-04-09T10:56:14.799226Z
0.0006259999999999999 ard2 0.987 0.649 0 5GFwJHucflHj46Hwet6qvc 2021-04-09T10:56:14.799226Z
0.145 ard12 0.986 0.597 1 1MgM0WDaoQ2A3bnIQTR6ty 2021-04-09T10:56:15.309345Z
0.145 ard12 0.986 0.597 1 1MgM0WDaoQ2A3bnIQTR6ty 2021-04-09T10:56:15.309345Z

I would like to have top-5 sensors by name according to some other value, let's say

of course if I do

select * from my_sensors order by prob DESC

then I have some duplicates by sensor name and LIMIT is not working because of this.

Upvotes: 0

Views: 55

Answers (1)

Brian Smith
Brian Smith

Reputation: 1315

What you could do in this case is use WITH which allows sub-queries:

WITH top_devices AS (select DISTINCT name, prob from my_sensors order by prob DESC)
select * from top_devices limit 5;

This will return two columns with the sensor name and prob column:

name prob
ard12 0.145
ard 0.0755
ard2 0.0006259999999999999
... ...

There are only three unique sensors in your example, but I presume the full dataset would give you top 5.

To get rid of dupes, the example query here is using DISTINCT

References:

Upvotes: 1

Related Questions