I want to sort the report by Company and Course because 1) we distribute reports by Company, and 2) we have students in more than one course.
Here is my SQL
SELECT u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(u.firstname , ' ' , u.lastname) AS 'Display Name',
aa.data AS Company, c.fullname AS Course,
cc.name AS 'Category',
CASE
WHEN gi.itemtype = 'course'
THEN CONCAT(c.fullname, ' - Total')
ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Grade,
FROM_UNIXTIME(gg.timemodified) AS TIME
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
JOIN prefix_user_info_data AS aa ON u.ID = aa.userid AND aa.fieldid=1
WHERE aa.data != ''
ORDER BY company, course, lastname, firstname
it is sorting by lastname, firstname, then course, ignoring Company
I´ve also tried ORDER BY aa.data, course, lastname, firstnamewith the same result
Any ideas?
