Q:

top 2 results on group by statement?

I have a table "record: id,name,score" and would like to query for the 2 highest scores per each name. I am using group by to get the highest score as : 
select name,max(score)as score from record group by name order by score

But I think its not possible to get the 2 highest scores using group by, how do I get them ? 

A:

 SELECT name, score FROM record R1
   WHERE (SELECT COUNT(DISTINCT score) FROM record R2
          WHERE R2.name = R1.name 
            AND R2.score >= R1.score) <= 2

Not especially performant (that is, may be kind of slow), but it should return what you're looking for.

A:

What you need is a ranking function which MySQL does not natively support at the moment. However, you can simulate it like so:
Select name, score, rnk
From  (
      Select name, score
        , @curRank := if(@name=name, if(@score=score, @curRank, @curRank + 1), 1) As rnk
        , @name := name
        , @score := score
      From  (
            Select name, score
            From record
              Cross Join ( Select @curRank := 0, @name := '', @score := -1 ) As Z1
            Order By name, score Desc
            ) as R
      ) As Z
Where rnk <= 2

SQL Fiddle

