CODEWITHSUNDEEP
phpmysql
Jake Wilson
Jake Wilson

Reputation: 91193

Select random row per distinct field value?

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.

Upvotes: 5

Views: 2286

Answers (4)

kasavbere
kasavbere

Reputation: 6003

Here is the solution:

select person, acting from personel where id in (
select lim from 
    (select count(person) c, min(id) i, cast(rand()*(count(person)-1) +min(id)
             as unsigned) lim from personel group by person order by i) t1
)

The table used in the example is below:

create table personel (
id int(11) not null auto_increment,
person char(16),
acting char(19),
primary key(id)

);

insert into personel (person,acting) values
('john','abd'),('john','aabd'),('john','adbd'),('john','abfd'),
('alex','ab2d'),('alex','abd3'),('alex','ab4d'),('alex','a6bd'),
('max','ab2d'),('max','abd3'),('max','ab4d'),('max','a6bd'),
('jimmy','ab2d'),('jimmy','abd3'),('jimmy','ab4d'),('jimmy','a6bd');

Upvotes: 0

bretterer
bretterer

Reputation: 5781

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

Upvotes: 6

AshBrad
AshBrad

Reputation: 492

My first response would be to use php to generate a random number:

$randId = rand($min, $max);

Then run a SQL query that only gets the record where your index equals $randID.

Upvotes: 0

rcurts
rcurts

Reputation: 45

You can limit the number of queries, and order by "rand()" to get your desired result.

Perhaps if you tried something like this:

SELECT name, some_info
  FROM test
 WHERE name = 'tara'
 ORDER BY rand()
 LIMIT 1

Upvotes: -1

Related Questions