#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3

    1 test for each student


    Hi,

    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:

    students:
    | student_id | is_active |

    studentgrades:
    | 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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    i don't get an error. just wrong results
    select test_student_id, test_id, max(test_score)
    from studentgrades
    right join students on test_student_id=student_id and is_active=1
    where test_date=20130102
    group by test_student_id
    having max(test_grade)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Code:
    SELECT s.student_id
         , x.test_id
         , x.test_grade
      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 
     RIGHT 
      JOIN students s 
        ON s.student_id = x.test_student_id 
     WHERE s.is_active = 1;
    or something like that
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    is there any more efficient way of doing this?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Efficient in what sense?
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by cafelatte
    Efficient in what sense?
    for all that is holy, and in the name of sanity, please rewrite your query using a LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    I would if I knew how!
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    select student_id, test_id, max(test_score)
    from students
    left outer join studentgrades on student_id=test_student_id and is_active=1
    where test_date=20130102
    group by test_student_id
    having max(test_grade)
    when it comes to right joins, everyone's a hater
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    That query will (still) give incorrect results

    (and where did test_score come from anyway?)
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    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?
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Probably, but it will likely be less efficient - if you know what I mean!

    Comments on this post

    • Jyncka agrees : ba-dum tish!
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by bobert123
    ... there some way to do this without 2 joins involving a nested select?
    sure -- it depends entirely on which columns you want to return
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    i ended up using the 2 joins with the nested select and it worked.

IMN logo majestic logo threadwatch logo seochat tools logo