CODEWITHSUNDEEP
mysqlsql
Samuel DR
Samuel DR

Reputation: 1231

top 2 results on group by statement?

I have a table "record: id,name,score" and would like to query for the 2 highest scores per each name. I am using group by to get the highest score as : 

select name,max(score)as score from record group by name order by score

But I think its not possible to get the 2 highest scores using group by, how do I get them ?

Upvotes: 4

Views: 829

Answers (3)

Thomas
Thomas

Reputation: 64674

What you need is a ranking function which MySQL does not natively support at the moment. However, you can simulate it like so:

Select name, score, rnk
From  (
      Select name, score
        , @curRank := if(@name=name, if(@score=score, @curRank, @curRank + 1), 1) As rnk
        , @name := name
        , @score := score
      From  (
            Select name, score
            From record
              Cross Join ( Select @curRank := 0, @name := '', @score := -1 ) As Z1
            Order By name, score Desc
            ) as R
      ) As Z
Where rnk <= 2

SQL Fiddle

Upvotes: 2

Larry Lustig
Larry Lustig

Reputation: 50998

 SELECT name, score FROM record R1
   WHERE (SELECT COUNT(DISTINCT score) FROM record R2
          WHERE R2.name = R1.name 
            AND R2.score >= R1.score) <= 2

Not especially performant (that is, may be kind of slow), but it should return what you're looking for.

Upvotes: 4

Rami Alshareef
Rami Alshareef

Reputation: 7160

select TOP (2) name,max(score)as score from record group by name order by score

EDIT: I just noticed its in mysql then

select name,max(score)as score from record group by name order by score LIMIT 2

Upvotes: 0

Related Questions