Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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 
like image 450
waitingkuo Avatar asked Apr 12 '13 10:04

waitingkuo


2 Answers

SELECT * FROM   test s WHERE          (             SELECT  COUNT(*)              FROM    test  f             WHERE f.name = s.name AND                    f.score >= s.score         ) <= 2 
  • SQLFiddle Demo
like image 165
John Woo Avatar answered Oct 07 '22 08:10

John Woo


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

like image 30
Taryn Avatar answered Oct 07 '22 10:10

Taryn