mysql - Limit result amount for each ID to x -

i trying in mysql, without making query multiple times (50 times, in case) through php foreach.

foreach($this->map_ids $key => $val) {     $this->db->query("select scores.profile_number, scores.score  scores                      left join players on scores.profile_number = players.profile_number                     scores.map_id = {'$val'}                     , scores.profile_number in (select profile_number players banned = 0) limit 10");     } 

this how looks approximately when retrieve scores without limit.

profile             score   map_id 76561198026851335   2478    47455 76561198043770492   2480    47455 ...                 ...     ... 76561198043899549   1340    47452 76561198048179892   1345    47452 ...                 ...     ... 

i want 10 entries (scores) each unique map_id.

this surprisingly difficult i've ended using user variables job, check out following demo. data structure simplified should enough going:

sql fiddle example

here sql may interested in skipping demo (hideous, know)

select * (       select profile_number, score, map_id       (          select            profile_number, score, map_id,            if( @prev <> map_id, @rownum := 1, @rownum := @rownum+1 ) rank,            @prev := map_id         scores          join (select @rownum := null, @prev := 0) r          order map_id       ) tmp        tmp.rank <= 10        ) s       join players p         on s.profile_number = p.profile_number 

basically, happening this:
order map_id
orders table map_id same ones together.

next assign rownumber each row using following logic:
if( @prev <> map_id, @rownum := 1, @rownum := @rownum+1 )
if previous row's map_id not equal current row's id, set row number = 1, otherwise increase rownumber 1.

finally, return rows have rownumber less or equal 10
where tmp.rank <= 10

hope makes little clearer you.