There is any equivalent for TOP and BOTTOM in SQL that available in influxdb?

user_12473258 0
Viewed 76

I am porting the queries in influx DB to timescaleDB (Postgres SQL). I am currently stuck in the TOP and BOTTOM functions. Is there any equivalent in Postgres SQL or any suggestions to achieve it?

For constant one, I did like that,

TOP('field', 1)    -> MAX('field')
BOTTOM('field', 1) -> MIN('field')

What about others like,

TOP('field', 5)
BOTTOM('field', 5)

Edit 1:

Does Using LIMIT with ORDER BY also work with GROUP BY because the limit is executed after group by Right What if want something like this

Thank You

timescaledb postgresql sql
1 Answers

Using window functions is probably the most versatile way to do this:

select *
from (
   select t.*, 
          dense_rank() over (partition by ??? order by ??? asc) as rnk
   from the_table t
) x
where x.rnk = 3; --<< adjust here

Rows in a relational database have no implied sort order. So "top" or "bottom" only makes sense if you also provide an order by. From your question is completely unclear what that would be.

Using order by .. asc returns the "bottom rows", using order by .. desc returns the "top rows"

If you want top/bottom for the entire table (instead of one row "per group"), the leave out the partition by

dense_rank() will return multiple rows with the same "rank" when the rows have the same highest (or lowest) value in the column you are sorting by. If you don't want that (and pick an arbitrary one from those "duplicates") then use row_number() instead.

user_3303150
Related