4

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 ?

Samuel DR
  • 1,215
  • 14
  • 28

3 Answers3

4
 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.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • This is getting closer, (though substatement needs to be R2), but if the 2 highest scores for a person are the same score, only 1 is added to the result. – Samuel DR Sep 20 '11 at 13:58
  • Yes, just noticed that. Hold on. . . – Larry Lustig Sep 20 '11 at 14:00
  • Okay, should handle ties in the second highest score per user now. . . – Larry Lustig Sep 20 '11 at 14:02
  • The first `DISTINCT` is still going to limit you to one row if the top 2 scores tie. – Tom H Sep 20 '11 at 14:04
  • Oops, meant to *move* the DISTINCT, not just add a second one. . . – Larry Lustig Sep 20 '11 at 14:05
  • 1
    Edited to final version. There is a issue of definition here, however — if the user's scores are 10, 10, 9, 8 are the "top two scores" for that user 10 and 10 or 10 and 9? Current query returns 10 and 9. To get 10 and 10, change `COUNT(DISTINCT Score)` to `COUNT(Score)`. That version would return 10 and 10, but it would have trouble with scores 10, 9, 9, 8 when it would return only 10. – Larry Lustig Sep 20 '11 at 14:10
  • Well, it should return 10,10 in the first example and 10,9 in the second example. I always try to do as much as possible in sql, would it be better to push it to the programming side and do: foreach name in table records -> get top 2 scores ? it is the east way to deal with this, but probably less performant. – Samuel DR Sep 20 '11 at 14:22
  • I also like to do everything I can in SQL (then encapsulate the logic in a VIEW for DRY purposes). I'm sure you can get what you want out of SQL, although the performance may fall off a bit. It might be easier to do if you have a unique ID or DateTime identifier on those `record` records. Do you? – Larry Lustig Sep 20 '11 at 14:29
  • "record: id,name,score" -> id is auto increment int PK. – Samuel DR Sep 20 '11 at 14:42
  • I'm working kind of quickly, but I think this version will do it for you. If not, I'll circle back later in the day. – Larry Lustig Sep 20 '11 at 14:51
  • this actually returns the entire table content, i appreciate your time input btw! – Samuel DR Sep 20 '11 at 15:08
2

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

Thomas
  • 63,911
  • 12
  • 95
  • 141
0

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

Rami Alshareef
  • 7,015
  • 12
  • 47
  • 75
  • 3
    No, this is high scores for the two highest scoring players. Sdry wants two scores returned *per player* and they should be the two highest for *that player*. – Larry Lustig Sep 20 '11 at 13:54
  • ops, seems the question was vague to me then – Rami Alshareef Sep 20 '11 at 13:57
  • Come on ppl! there were at lest 4 answers say the same thing my post do, all removed but still mine here! and plus I got downvoting for something that I understand it wrong! this isn't fair! – Rami Alshareef Sep 20 '11 at 14:03
  • I thought it was pretty clear (at least, the part in bold). But I think it's not very kind to rate down this answer, which is a genuine attempt to help. – Larry Lustig Sep 20 '11 at 14:03
  • Rami is right, a lot of people asnwered wrong and deleted once they realized. I added the bold after all the wrong answers, but did not modify text. – Samuel DR Sep 20 '11 at 14:06