select random value from each type

Question

I have two tables, rating:

+-----------+-----------+-------------+----------+
| rating_id | entity_id | rating_code | position |
+-----------+-----------+-------------+----------+
|         1 |         1 | Quality     |        0 |
|         2 |         1 | Value       |        0 |
|         3 |         1 | Price       |        0 |
+-----------+-----------+-------------+----------+

And rating_option

+-----------+-----------+------+-------+----------+
| option_id | rating_id | code | value | position |
+-----------+-----------+------+-------+----------+
|         1 |         1 | 1    |     1 |        1 |
|         2 |         1 | 2    |     2 |        2 |
|         3 |         1 | 3    |     3 |        3 |
|         4 |         1 | 4    |     4 |        4 |
|         5 |         1 | 5    |     5 |        5 |
|         6 |         2 | 1    |     1 |        1 |
|         7 |         2 | 2    |     2 |        2 |
|         8 |         2 | 3    |     3 |        3 |
|         9 |         2 | 4    |     4 |        4 |
|        10 |         2 | 5    |     5 |        5 |
|        11 |         3 | 1    |     1 |        1 |
|        12 |         3 | 2    |     2 |        2 |
|        13 |         3 | 3    |     3 |        3 |
|        14 |         3 | 4    |     4 |        4 |
|        15 |         3 | 5    |     5 |        5 |
+-----------+-----------+------+-------+----------+

I need a SQL query (not application level, must stay in the database) which will select a set of ratings randomly. A sample result would look like this, but would pick a random value for each rating_id on subsequent calls:

+-----------+-----------+------+-------+----------+
| option_id | rating_id | code | value | position |
+-----------+-----------+------+-------+----------+
|         1 |         1 | 1    |     1 |        1 |
|         8 |         2 | 3    |     3 |        3 |
|        15 |         3 | 5    |     5 |        5 |
+-----------+-----------+------+-------+----------+

I'm totally stuck on the random part, and grouping by rating_id has been a crap shoot so far. Any MySQL ninjas want to take a stab?

Thanks, Joe

---

EDIT: I've tried rand() in a bunch of combinations, and I'm sure that it will be necessary to create the randomness of the result, but I cannot figure out how to return one random row for each of the rows in rating. I cannot use order by rand() limit 1 because I need three rows, and order by rand() limit 3 won't give me one of each rating_id, which is the ultimate goal. I need a combination of rand() and either subqueries or joins so that I can ensure one of each rating_id.

Answer 1

Have you looked into the rand() function?

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

http://www.petefreitag.com/item/466.cfm

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

Sample code

select *
from rating_option
group by rating_id
order by rand()

Answer 2

You could use the rand() function to do sorting in a select on the rating table.

For example:

select rating_id from rating order by rand() limit 1

Answer 3

Alright, a little messy, but seems to do the job. Someone may know what they're doing better than I do that can clean this up:

SELECT random.rating_id, random.rand_option_id, r3.code, r3.value, r3.position
FROM
    (SELECT r.rating_id,
        (SELECT r2.option_id
         FROM rating_option r2
         WHERE r2.rating_id = r.rating_id
         ORDER BY RAND() LIMIT 1) AS 'rand_option_id'
     FROM rating_option r
     GROUP BY r.rating_id
    ) random
    LEFT JOIN rating_option AS r3 ON r3.option_id = rand_option_id

Results (varies every time, of course):

+-----------+----------------+------+-------+----------+
| rating_id | rand_option_id | code | value | position |
+-----------+----------------+------+-------+----------+
|         1 |              4 |    4 |     4 |        4 |
|         2 |              6 |    1 |     1 |        1 |
|         3 |             13 |    3 |     3 |        3 |
+-----------+----------------+------+-------+----------+

Answer 4

As clarified in your comments, and the other posts above

select * from rating_option order by rand()

will return all records in a random order... However, if you want only X number, then inclue that as the limit as noted by others

select * from rating_option order by rand() limit 5 (or whatever number)