In SQL, how to select the top 2 rows for each group

In MySQL, you can use user-defined variables to get a row number in each group:

select name, score
from
(
  SELECT name,
    score,
    (@row:=if(@prev=name, @row +1, if(@prev:= name, 1, 1))) rn
  FROM test123 t
  CROSS JOIN (select @row:=0, @prev:=null) c
  order by name, score desc 
) src
where rn <= 2
order by name, score;

See Demo

SELECT *
FROM   test s
WHERE 
        (
            SELECT  COUNT(*) 
            FROM    test  f
            WHERE f.name = s.name AND 
                  f.score >= s.score
        ) <= 2

• SQLFiddle Demo

If you don't mind having additional column then you can use the following code:

SELECT Name, Score, rank() over(partition by Name order by Score DESC) as rank
From Table
Having rank < 3;

Rank function provides rank for each partition, in your case it is name