mar tin mar tin - 15 days ago 7
SQL Question

SQL: partitioning by column and randomly order results within the partitions

Suppose I have table

DESCRIPTION SHOP
'tomato pizza' 'Joe'
'vanilla ice cream' 'Joe'
'oranges' 'Mary'
'newspapers' 'Mary'
'soap' 'Bill'
'chips' 'Joe'
...


So, each shop (identified by its owner) sells some products and each product appears in the table with a text description.

What I want to do is pulling 10 products from each shop, randomly chosen (for that shop), in one go.
I know I can use a
ORDER BY random()
but how can I use it in such a way that first a separation by shop (a
GROUP BY
/
PARTITION BY
?) takes place and then randomisation is on the separated results?

Referring particularly to Redshift, which is what I'm using and should be same as PostgreSQL syntax.

Answer

The row_number window function should do the trick:

SELECT description, shop
FROM   (SELECT description, shop, 
        ROW_NUMBER() OVER (PARTITION BY shop ORDER BY RANDOM()) AS rn
        FROM   mytable) t
WHERE  rn <= 10