No doubt everybody has solved it before, but ten seconds on Google didn't show any obvious results, so here we go: How to pick the top 5 records of each group. And we'll do it without subqueries, for the pre-4.1 folks.
We'll define "top 5" as "the first 5 records when ordering by a field called `rating`".
SELECT @oldGroup := 0, @count := 0; SELECT @oldGroup := groupId, theTable.* FROM theTable HAVING (@count := IF(groupId <=> @oldGroup, @count+1, 0)) < 5 ORDER BY groupId, rating;
UPDATE: At last, the version with a subquery and no user variables. Someday, when the 'LIMIT & IN/ALL/ANY/SOME subquery' restriction is lifted, I'll give that correlated subquery that Sheeri mentioned a shot.
SELECT theTable.* FROM theTable JOIN ( SELECT t1.groupId, t1.rating, COUNT(t2.groupId) AS theCount FROM theTable t1 LEFT JOIN theTable t2 ON t1.groupId = t2.groupId AND t1.rating > t2.rating GROUP BY t1.groupId, t1.rating HAVING theCount < 5 ) AS dt USING (groupId, rating);