CODEWITHSUNDEEP
mysqlsqlmaxgreatest-n-per-group
ElKamina
ElKamina

Reputation: 7817

Finding the record with maximum value in SQL

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.

Upvotes: 3

Views: 3693

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115650

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
       )

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 755064

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;

Upvotes: 4

MorganP
MorganP

Reputation: 13

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

Upvotes: 0

Related Questions