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

Solution 1

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

• SQLFiddle Demo

Solution 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

Solution 3

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

Solution 4

insert into test values('willy',1)
insert into test values('willy',2)
insert into test values('willy',3)
insert into test values('zoe',4)
insert into test values('zoe',5)
insert into test values('zoe',6)


;with temp_cte
as (
    select Name, Score,
       ROW_NUMBER() OVER (
          PARTITION BY Name
          ORDER BY Score desc
       ) row_num
    from test
)
select * from temp_cte
where row_num < 3

View more solutions

Comments

[waitingkuo]

I'm afraid that it's not what I expect

[devilcrab]

yea i was just giving method you can do it simply, if you have maintained ID for each row (primary key) it will work more and you will have more functions in hand. In the way you need its long code and also , it will be harder for you to use anything else over it in future.

[waitingkuo]

Thank you for this solution, I'm still new in SQL. Hope that I can understand this one in the future :)

[Taryn]

@waitingkuo Unfortunately MySQL does not have windowing functions which would allow you to easily assign a row number to each rows in a group.

[asm234]

@bluefeet thanks this is a very nice solution, it worked very fast even on 30k something rows, my earlier solution using joins was very slow

[Admin]

but that may cause performance issue. is there any other quicker way to implement this query?

[Kevin Sylvestre]

This does indeed cause pretty heavy performance problems (the sub-select is quadratic).

[mdubez]

This can be done linearly, see "MySQL Query to Get Top 2" here sqlines.com/mysql/how-to/get_top_n_each_group

[mdubez]

Is this safe? MySQL states "the order of evaluation for expressions involving user variables is undefined." and doesn't that mean @prev:=name could be evaluated before your case statement and thus the case statement would artificially be true? Or am I missing something? See dev.mysql.com/doc/refman/5.5/en/user-variables.html

[Nigel Fds]

I'm getting error: Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'order'.

[neverendingqs]

There shouldn't be a comma after "Name"

[hc_dev]

Good first answer! Please test your code before posting (missing semicolons after inserts). Explain it (using comments) as well as used concepts, e.g. WITH and Common Table Expersion.