January 2nd, 2013, 03:31 AM
1 test for each student
I'm trying to write a query that finds for each active student the test from today with the highest grade.
This seems like it should be simple, but it's escaping me.
tables are as below:
| student_id | is_active |
| test_id | test_student_id | test_grade | test_date
if possible, i'd also like to include the active students who don't have tests in the results
the test_grade is an integer, so MAX(test_grade) should work.
January 2nd, 2013, 03:50 AM
first, you need a LEFT OUTER JOIN
then you need MAX as well as GROUP BY
give it a try, and let us know if you get any error messages
January 2nd, 2013, 04:17 AM
i don't get an error. just wrong results
January 2nd, 2013, 05:21 AM
or something like that
FROM studentgrades x
JOIN (SELECT test_student_id,test_date,MAX(test_grade) max_test_grade FROM studentgrades WHERE test_date = '2013-01-02' GROUP BY test_student_id) y
ON y.test_student_id = x.test_student_id
AND y.max_test_grade = x.test_grade
AND y.test_date = x.test_date
JOIN students s
ON s.student_id = x.test_student_id
WHERE s.is_active = 1;
January 2nd, 2013, 05:55 AM
is there any more efficient way of doing this?
January 2nd, 2013, 06:17 AM
January 2nd, 2013, 06:22 AM
for all that is holy, and in the name of sanity, please rewrite your query using a LEFT OUTER JOIN
Originally Posted by cafelatte
January 2nd, 2013, 06:26 AM
January 2nd, 2013, 07:38 AM
when it comes to right joins, everyone's a hater
January 2nd, 2013, 08:09 AM
That query will (still) give incorrect results
(and where did test_score come from anyway?)
January 2nd, 2013, 08:31 AM
it's a typo. should say test_grade.
i know it won't work. i'm just changing it to a left join to avoid the complaints.
by efficient, i mean is there some way to do this without 2 joins involving a nested select?
January 2nd, 2013, 09:15 AM
Probably, but it will likely be less efficient - if you know what I mean!
Comments on this post
January 2nd, 2013, 10:23 AM
sure -- it depends entirely on which columns you want to return
Originally Posted by bobert123
January 6th, 2013, 04:03 AM
i ended up using the 2 joins with the nested select and it worked.