Limit Max and Group by returned too many rows

Question

I'm using a SQL Server database.

Given the following query, I'm trying to get the highest score for each class. The Scores table has 50 rows for two classes so I want a total of 2 rows. But, because I have the Scores.Id, it returns each row for the Scores since Scores.Id is unique. Of course the simple solution is to remove the Scores.Id column except I need to know the Scores.Id to do additional lookup.

SELECT 
    Class_Id, Scores.Id, MAX(Scores.ClassScore) AS Score
FROM  
    Classes 
INNER JOIN
    Scores ON Classes.Id = Scores.Class_Id                
GROUP BY 
    Scores.Class_Id, Scores.Id

Answer 1

Is this what you want?

select s.*
from (select s.*,
             max(s.classscore) over (partition by s.class_id) as max_classscore
      from scores s
     ) s
where classscore = max_classscore;

Given the information that you want, the join to Classes is superfluous.

Answer 2

The max score by class_id is simply

select class_id, max(classScore) score from scores
group by class_id

If you then need to know which rows in the scores table had the max score you can do a join. You may get more than two rows if there's more than one equal max score per class.

select id, class_id, classscore from
scores s
inner join
(
    select class_id, max(classScore) score from scores
    group by class_id
)
t
on t.class_id = s.class_id and t.score = s.classScore

Or I might use a cte

with maxScores as
(
    select class_id, max(classScore) score from scores
    group by class_id
)
select id, class_id, classscore from
scores s
on maxScores.class_id = s.class_id and maxScores.score = s.classScore

Answer 3

This looks like a job for the rank window function:

SELECT Class_Id, Id, Score
FROM   (SELECT Class_Id, Scores.Id AS Id, Score, 
               RANK() OVER (PARTITION BY Class_Id, Scores.Id
                            ORDER BY Score DESC) AS rk
        FROM   Classes 
        JOIN   Scores ON Classes.Id = Scores.Class_Id) t
WHERE  rk = 1