Problems selecting columns with aggregates (SQL Server)

Question

I'm seriously stuck. Please bear with me though because I'm new to databases.:)

Anyway, I need to display the StudentID, the subject where the student has the highest grade in, and the grade of that subject.

Here's the code I have:

SELECT 
    Grades.Student_ID,
    Subject.Subject_Code,
    MAX(Grades.Grade)
FROM 
    Grades
LEFT JOIN 
    Subject ON Grades.Subject_ID = Subject.Subject_ID
GROUP BY 
    Grades.Student_ID

But it has this error:

'Subject.Subject_Code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

But I can't include Subject_Code in GROUP BY because the results will be different.

What can I do to only show the

Student_ID || (subject with highest grade) || (grade of that subject)

How can I work around this error?

you need to `group by grades.student_id, subject.subject_code` to get rid of that error. also, are you SURE you're using mysql? by default mysql allows your query without that error — pala_, Apr 18 '15 at 10:47
I mentioned that I made a mistake. Its SQL Server, not MySql. Sorry for that — ckee, Apr 18 '15 at 11:03
possible duplicate of [What does the "invalid, not contained in either an aggregate function" message mean?](http://stackoverflow.com/questions/18258704/what-does-the-invalid-not-contained-in-either-an-aggregate-function-message-m) — Tanner, Apr 29 '15 at 13:34

StuartLC · Accepted Answer · 2015-04-18T20:04:28.283

It seems you are looking for a group wise maximum. Here's one approach joining back to a derived table containing the top grade for each student (This approach should work on most RDBMS, including MySql):

SELECT X.Student_ID,
    s.Subject_Code,
    x.TopGrade
FROM
(
  SELECT 
    Grades.Student_ID,
    MAX(Grades.Grade) AS TopGrade
  FROM Grades
  GROUP BY Grades.Student_ID
) x
INNER JOIN Grades g
ON g.Student_ID = x.Student_ID AND g.Grade = x.TopGrade
LEFT JOIN Subject s
      ON g.Subject_ID = s.Subject_ID

If the same student has two or more marks with exactly the same grade, it will return all Subjects.

Here's my original answer, which will work on SQL Server

SELECT x.Student_ID, x.Subject_Code, x.Grade
FROM
(
  SELECT 
    Grades.Student_ID,
    Subject.Subject_Code,
    RANK() OVER (PARTITION BY Grades.Student_ID ORDER BY Grades.Grade DESC) AS [Rank],
    Grades.Grade
  FROM Grades
  LEFT JOIN Subject
      ON Grades.Subject_ID = Subject.Subject_ID
) x
WHERE x.[Rank] = 1;

SqlFiddle of both the above queries here. In addition, there is an example with ROW_NUMBER with an additional arbitrary ORDER BY to pick one top subject when the student has equal marks in two or more subjects.

Thanks it works! But how can I exclude Rank from showing in the results? Changing * to specific columns doesnt work. — ckee, Apr 18 '15 at 11:31
I've updated `SELECT x.Student_ID, x.Subject_Code, x.Grade` - the trick is that we've aliased the derived table as 'x', so the original table names are lost - this is most likely where things went wrong. — StuartLC, Apr 18 '15 at 11:36
+1 - Though I do have one recommendation. Only include `Grades` in the inline view (Or use a CTE) and call it `RankedGrades`. Then join `Subject` outside rather than inside the inline view or CTE. This is mainly a readability / maintainability change (encapsulating minimal behaviour, decoupling the Subject lookup from the Ranking), but in other more complex scenarios it can have performance benefits too. — MatBailie, Apr 18 '15 at 11:49

Problems selecting columns with aggregates (SQL Server)

1 Answers1