# rjFinalGrades
SELECT DISTINCT c.shortname AS CourseName,
                c.idnumber AS CourseIDNum,
                IF (c.shortname LIKE '%Online%'
                    OR c.shortname LIKE '%OL%',
                                        1,
                                        0) AS Online,
                   u.lastname AS LastName,
                   u.firstname AS FirstName,
                   gg.userid AS StudentID,
                   gg.finalgrade AS FinalGrade
FROM prefix_grade_items gi
JOIN prefix_grade_grades gg ON gi.id = gg.itemid
JOIN prefix_user u ON u.id = gg.userid
JOIN prefix_user_enrolments ue ON u.id = ue.userid
JOIN prefix_enrol AS en ON en.courseid = gi.courseid
JOIN prefix_role_assignments AS asg ON gg.userid = asg.userid
JOIN prefix_course c ON c.id = gi.courseid
WHERE itemtype = 'course'
  AND ue.status = 0
  AND asg.roleid = 5
ORDER BY c.idnumber DESC,
         gg.finalgrade DESC,
         u.lastname,
         u.firstname
