How to pull 3 random records per category in Presto SQL?

I have the following table:

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

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.

Advertisement

Answer