jeudi 27 avril 2017

Random sampling of 30 rows for each column

I have a very simple MySQL table as below:

CREATE TABLE tdata (
    id varchar(256),
    color varchar(256)
);

This table has data like

bill,orange
bill,orange
bill,umber
(...)
bill,yellow
jerry,blue
jerry,purple
(...)
jerry,blue

I'm looking to select a unique random sample of 20 colors for each id, such that the output would look like:

bill,orange
bill,umber
(...)
bill,chartreuse
jerry,blue
(and so on)

The full table is almost 100k rows, so I can't specify each ID in the query. Is there an elegant MySQL query to do this?




Aucun commentaire:

Enregistrer un commentaire