Search code examples
mysqlsqlgroup-bygreatest-n-per-group

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

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
Solution 
  • SELECT *
FROM   test s
WHERE 
        (
            SELECT  COUNT(*) 
            FROM    test  f
            WHERE f.name = s.name AND 
                  f.score >= s.score
        ) <= 2