Select random row per distinct field value?

Question

I have a MySQL query that results in something like this:

person | some_info 
==================
   bob | pphsmbf24
   bob | rz72nixdy
   bob | rbqqarywk
  john | kif9adxxn
  john | 77tp431p4
  john | hx4t0e76j
  john | 4yiomqv4i
  alex | n25pz8z83
  alex | orq9w7c24
  alex | beuz1p133
   etc...

(This is just a simplified example. In reality there are about 5000 rows in my results).

What I need to do is go through each person in the list (bob, john, alex, etc...) and pull out a row from their set of results. The row I pull out is sort of random but sort of also based on a loose set of conditions. It's not really important to specify the conditions here so I'll just say it's a random row for the example.

Anyways, using PHP, this solution is pretty simple. I make my query and get 5000 rows back and iterate through them pulling out my random row for each person. Easy.

However, I'm wondering if it's possible to get what I would from only a MySQL query so that I don't have to use PHP to iterate through the results and pull out my random rows.

I have a feeling it might involve a BUNCH of subselects, like one for each person, in which case that solution would be more time, resource and bandwidth intensive than my current solution.

Is there a clever query that can accomplish this all in one command?

Here is an SQLFiddle that you can play with.

Answer 1

To get a random value for a distinct name use

SELECT r.name, 
(SELECT r1.some_info FROM test AS r1 WHERE r.name=r1.name ORDER BY rand() LIMIT 1) AS     'some_info' 
FROM test AS r 
GROUP BY r.name ;  

Put this query as it stands in your sqlfiddle and it will work

Im using r and r1 as table alias names. This will also use a subquery to select a random some_info for the name

SQL Fiddle is here