I have the following table:
x
category_id name
5 rob
5 james
5 kobert
5 ken
4 jef
3 rok
I want to write a query that gives random 3 names per each category.
Possible output for example can be:
category_id name
5 rob
5 james
5 ken
4 jef
3 rok
I know it should be something like:
SELECT category_id, name
FROM(
SELECT category_id, name, RANDOM() Over (Partition By category_id) rn
FROM dataset )
WHERE rn<=3
but I can’t find the proper syntax to do this. It gives me
Not a window function: random
Advertisement
Answer
Use row_number()
:
SELECT category_id, name
FROM (SELECT category_id, name,
row_number() Over (Partition By category_id order by random()) as seqnum
FROM dataset
)
WHERE seqnum <= 3;
That is, the random()
goes in the order by
.