selecting records 1

[133tcamel]

Hi,
Is there a way to write a MySQL query so that I can make sure that not more than 10 records of the same value are returned.

I mean suppose I write the following query for some table:

select first_name, last_name from table ORDER BY RAND() LIMIT 100

is there some way I can modify the above query to make sure that everytime out of the 100 random rows returned, there are only 10 (or less) rows which contain the same surname

I mean anyway to at least make sure that there are not more than 10 anderson's in the above 100 rows returned? If anyone knows of any way of doing it, then please share it with me. I would really appreciate any help

Thanks in advance,
san.

---
cheers!
san

print length "answer to life the universe and everything";

 

[hvass]

i can only do it in 3 steps but perhaps better to use Perl/PHP anyway

# add a running number to each record and sort in lastname order
drop table if exists test.indata_urn;
create table test.indata_urn select * from test.indata order by last_name;
alter table test.indata_urn
add column run int(8) unsigned primary key auto_increment,
add index last_name(last_name);

# calculate the minimum and maximum run number
drop table if exists test.indata_maxmin;
create table test.indata_maxmin
select
last_name,
min(run) as minrun,
if(max(run)-min(run)>10,min(run)+9, max(run)) as maxrun
from test.indata_urn
group by last_name;

# select only records between minrun and maxrun
drop table if exists test.indata_max10;
create table test.indata_max10
select
a.*
from test.indata_urn a inner join test.indata_maxmin b using(last_name)
where run between minrun and maxrun;

 

[133tcamel]

that took me like 5 minutes to comprehend! This code owns and works like such a charm! I guess will also look into an alternative route with perl sometime, but its good enough for now! thanks dude!

---
cheers!
san

print length "answer to life the universe and everything";