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

Question

I have a table as following:

NAME    SCORE ----------------- willy       1 willy       2 willy       3 zoe         4 zoe         5 zoe         6 

Here's the sample

The aggregation function for group by only allow me to get the highest score for each name. I would like to make a query to get the highest 2 score for each name, how should I do?

My expected output is

NAME    SCORE ----------------- willy       2 willy       3 zoe         5 zoe         6 

Answer 1

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

• SQLFiddle Demo

Answer 2

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