vendredi 25 novembre 2016

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.




Aucun commentaire:

Enregistrer un commentaire