How to query distinct rows for every but one fields in QuestDB?

user_15463410 0
Viewed 108

I have a table like

A int,
B int,
C timestamp

and there are multiple rows where B and C are same but A is different. How can select one of the rows only (with any of A values) per each (B, C) unique pair in QuestDB?

If I had window function support I would

select A, B, C from (
select A, B, C,
 row_number() OVER (partition by B, C) as rownum
from tbl) temp
where temp.rownum = 1

but unfortunately window functions are not supported in QuestDB

questdb
1 Answers

You can group by column you want to keep distinct and select first() on columns you want to choose random value from, like this

select first(A) as A, B, C
from tbl

Group by is not needed in QuestDB, it will figure out automatically that grouping is by B,C

user_149546330
Related