How do I select 10 random rows from a MYSQL Database, with unique values in column x?

Question

The goal of this query is to pick 10 winners from a mysql database of entries into a contest. The columns are "product_id", "uid" and "winner".

Winner is always 0 (for now, as winners haven't been picked), uid is a unique index (one entry per person), and product_id is one of 10 different product id's. To enter the contest, the user logged in, and chose which one of the 10 products they wanted to win.

There are 1000 rows in the database, with about 100 rows each for each of the 10 product id's.

I'd like to have one query that chooses one random entry for each of the 10 different product id's. The query below is selecting 10 random entries, but some of the product id's are the same.

SELECT DISTINCT product_id, uid FROM database.table WHERE winner = 0 ORDER BY RAND( ) LIMIT 0 , 10

How can I improve this query to work in the way I need?

Possible duplicate of [Group By value RAND()](http://stackoverflow.com/questions/12725363/group-by-value-rand) — Grzegorz Adam Kowalski, Oct 05 '15 at 07:29

score 0 · Accepted Answer · answered Jan 10 '13 at 15:21

SELECT product_id, uid
FROM (  SELECT product_id, uid
        FROM database.table
        WHERE winner = 0
        ORDER BY RAND()) AS h
GROUP BY product_id

GROUP BY with this x column you're talking about should solve this I think. Actually, you quite rarely have to use DISTINCT due to the more precise GROUP BY.

How do I select 10 random rows from a MYSQL Database, with unique values in column x?

1 Answers1