Return 2 random records per group......?

[BiggerBrother]

Ok, so its friday afternoon again, and here's the issue at the end of a long week!

I have a table, with IDs and TYPEs. What I need to be able to do is return a recordset with 2 random records for each TYPE. There are 30,000 records in the db, and 150 types, and I'm trying to avoid running 150 queries to grab 2 random records at a time.

Is this even possible?

Many thanks

BB

 

[hvass]

Given you have a table like this

use test;
drop table if exists test.t1;
create table test.t1 (run int(8), vset char(1)) type=myisam;
insert into test .t1 (run, vset) values 
(1,'A'),(2,'A'),(3,'A'),(4,'A'),(5,'A'),(6,'A'),(7,'A'),(8,'A'),(9,'A'),
(1,'B'),(2,'B'),(3,'B'),(4,'B'),(5,'B'),(6,'B'),(7,'B'),(8,'B'),(9,'B'),
(1,'C'),(2,'C'),(3,'C'),(4,'C'),(5,'C'),(6,'C'),(7,'C'),(8,'C'),(9,'C')
;
select * from test.t1 order by vset,run;

You could use auto increment feature of myisam tables

drop table if exists test.t2;
create table test.t2 (run int(8), vset char(1), id int(8)  auto_increment, primary key (vset,id)) type=myisam;
insert into test .t2 (run,vset)
select 
	run,vset 
from test.t1
order by vset,rand();
select * from test.t2 order by vset,id;

##to return random sample of 2 records for each vset

select * from test.t2 where id<3 order by vset,id;

run vset id
8 A 1
5 A 2
9 B 1
7 B 2
8 C 1
2 C 2