CODEWITHSUNDEEP
questdb
Nick The Greek
Nick The Greek

Reputation: 453

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

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

Upvotes: 2

Views: 245

Answers (1)

djbobo
djbobo

Reputation: 547

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

Upvotes: 1

Related Questions