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

greatest-n-per-group, group-by, mysql, sql

Solution

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

- SQLFiddle Demo

Problem

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 ```

Original source

Related problems