3

I have the following table:

Class, Name, Score
1, Anna, 34
1, Andy, 80
2, Brooke, 90
2, Brad, 70
3, Charles, 67
3, Christina, 66

How to I find the 'Name' with maximum 'Score' in each 'Class' ?

Required Output:

Class, Name, Score
1, Andy, 80
2, Brooke, 90
3, Charles, 67

This is for MySQL.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
ElKamina
  • 7,747
  • 28
  • 43
  • 2
    Looks like homework. Hint is: use group by and max – Steven Jan 04 '12 at 00:04
  • I added the `greatest-n-per-group` tag, this question is very popular on StackOverflow. Follow that tag link for many solutions. – Bill Karwin Jan 04 '12 at 00:09
  • 1
    @Steven Nope. I agree this a (hastily created) toy problem. The actual problem is much complicated. But I am stuck on this part of the problem. – ElKamina Jan 04 '12 at 00:16
  • Sorry ElKamina, sometimes its hard to tell the difference when attributes reflect typical school homework names. – Steven Jan 04 '12 at 00:24
  • @Steven I am still interested in knowing your solution as it looks simpler than the one that is offered. – ElKamina Jan 04 '12 at 00:41

3 Answers3

4
WITH ClassScores AS
(
    SELECT 1 AS class, 'Anna' AS name, 34 AS score 
    UNION
    SELECT 1, 'Andy', 80  
    UNION
    SELECT 2, 'Brooke', 90  
    UNION
    SELECT 2, 'Brad', 70  
    UNION
    SELECT 3, 'Charles', 67  
    UNION
    SELECT 3, 'Christina', 66 
)

SELECT C1.Class, C1.Name, C1.Score
  FROM ClassScores AS C1
  JOIN (SELECT Class, MAX(Score) AS MaxScore
          FROM ClassScores
         GROUP BY Class
       ) AS C2
    ON C1.Class = C2.Class
   AND C1.Score = C2.MaxScore
 ORDER BY C1.Class;
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • Thanks, @AbeMiessler. I note that if two (or more) people scored the same maximum score in a class, then you will get several rows for that class - one for each of the people who scored the maximum. This is probably better than randomly choosing who is chosen as the archetype for a particular class - or you need extra criteria to distinguish between the putative valedictorians. – Jonathan Leffler Jan 04 '12 at 00:18
  • Thanks on adding CTE it makes these answers much more valuable. – Steven Jan 04 '12 at 00:23
  • It works, but with two caveats. One, it takes two passes. Two, as noted by yourself, it returns multiple entries for the same class in case of ties. I am new to SQL and I find it shocking that such a simple task cannot be achieved by MySQL with a single (pass) query :( – ElKamina Jan 04 '12 at 00:39
  • It can't be done in one pass because establishing the maximum scores requires one pass and then finding the corresponding rows requires another - in SQL. If you were using C-ISAM or a similar package, you could perhaps do better if there was an appropriate index to use, but you'd have to be careful about the cost of maintaining that index vs the amount of use you actually make of it. (Appropriate index means an index on 'class, score', optionally with 'name' afterwards, which would give you an index-only query). But it would require some modest coding skills to get that right; SQL is easier. – Jonathan Leffler Jan 04 '12 at 00:49
1

Another way - if the ClassScores has a (hidden) PRIMARY KEY:

SELECT 
    cs.Class
  , cs.Name
  , cs.Score
FROM 
      ( SELECT DISTINCT Class 
        FROM ClassScores 
      ) AS csd
  JOIN 
      ClassScores AS cs
    ON cs.PK = 
       ( SELECT csm.PK
         FROM ClassScores csm
         WHERE csm.Class = csd.Class
         ORDER BY csm.Score DESC
         LIMIT 1
       )
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
0

Use UNION and then you can use three select statements individually. It will clean up the code nicely.

Try..

select class, name, max(score) as "Score" from yourTable where class=1
UNION
select class, name, max(score) as "Score" from yourTable where class=2
UNION
select class,name,max(score) as "Score" from yourTable where class=3
MorganP
  • 13
  • 4