Member Avatar for 54uydf

Hi, I can't figure out how to write this query, I'm sure it's something silly, but I just can't figure it out!!
I have a table with student's grades in different classes they took,
example-

studId, classId, yearClassTaken, grade
1, 11, 2008, 90
1, 12, 2008, 78
1, 13, 2007, 30
1, 13, 2008, 70
1, 13, 2009, 80
2, 11, 2009, 65
......
....
...

I want to get every class only ONCE, and I want to get the LAST time the class was taken, so that later I can calculate the avg of each student. so in my example above, student 1 took the class 13 three times, so I want to get this row:
1, 11, 2008, 90
1, 12, 2008, 78
1, 13, 2009, 80
2, 11, 2009, 65

I put a max(yearClassTaken) to get the latest year but it gives me the grade of another year!
1, 13, 2011, 70

what am I doing wrong?

SELECT studId,
classId,
max(yearClassTaken) as year,
grade,
FROM `tblstudent_grades`

p.s I don't want to put max() on grade, because the point is not to get the highest grade, but rather the last grade.

  • 2 Contributors
  • 1 Reply
  • 376 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by urtrivedi

Dani AI

Generated

Short diagnosis: the problem is mixing an aggregate (MAX(yearClassTaken)) with non-aggregated columns. MySQL historically lets you select non-aggregated columns without grouping and returns an arbitrary row’s value, so the grade you see may come from a different year. The join approach offered by is a common fix, but there are other clear options depending on your MySQL version and whether you need tie-breaking when the same class/year appears more than once.

Correlated-subquery (works on older MySQL too):

SELECT sg.studId, sg.classId, sg.yearClassTaken, sg.grade
FROM tblstudent_grades sg
WHERE sg.yearClassTaken = (
  SELECT MAX(yearClassTaken)
  FROM tblstudent_grades
  WHERE studId = sg.studId AND classId = sg.classId
);

MySQL 8+ (cleanest and easiest to extend): use window functions to pick the latest row per student/class and add a tie-breaker (id or timestamp) if needed:

SELECT studId, classId, yearClassTaken, grade
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY studId, classId
                               ORDER BY yearClassTaken DESC, id DESC) rn
  FROM tblstudent_grades
) x
WHERE rn = 1;

To get each student’s average of their latest grades, wrap either of the above as a derived table and AVG(grade) GROUP BY studId. Practical tips: add an index on (studId, classId, yearClassTaken) to speed the queries, and include a monotonic column (auto-increment id or timestamp) if you need deterministic tie-breaking for same-year entries.

Member Avatar for urtrivedi 
SELECT a.studId,
a.classId,
a.yearClassTaken as year,
a.grade,
FROM `tblstudent_grades` a inner join 
(SELECT studId,
classId,
max(yearClassTaken) as year,
FROM `tblstudent_grades` group by studId,classId) b on a.studid=b.studid and a.classid=b.classid and a.yearClassTaken =b.year
Edited by urtrivedi because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.