Re: Sort (ORDER BY) by a comma-separated list of related fields using MySQL 4.1?

[Phil Powell]

SELECT MIN(s.id) AS id, s.student_first_name, s.student_mi,
                s.student_last_name, s.major, s.gpa, s.school_year_other,  
              s.application_date, s.modification_date, s.graduation,  
             y.school_year_alt_display, cc.comments,  

            IF(s.school_year_id = 0, s.school_year_other, y.school_year_alt_display) AS school_year_name,       

            IF(s.student_ethnicity_interest_other IS NOT NULL AND s.student_ethnicity_interest_other != '',           
                concat(s.student_ethnicity_interest_other, ', ', e.ethnicity_name), 
                e.ethnicity_name) AS ethnicity_name 

FROM student s,  completion_comments cc,  ethnicity e, student_ethnicity_interest_assoc seia

LEFT OUTER JOIN school_year y on s.school_year_id = y.id 

WHERE s.has_completion_reference_letter1 = cc.has_reference_letter1
  AND s.has_completion_reference_letter2 = cc.has_reference_letter2
  AND s.has_school_transcript = cc.has_school_transcript
  AND s.id = seia.student_id  AND seia.ethnicity_id = e.id

 GROUP BY id, s.student_first_name, s.student_mi, s.student_last_name, s.major, s.gpa, s.school_year_other, s.application_date, s.application_date, s.modification_date, s.graduation, y.school_year_alt_display, cc.comments, school_year_name, ethnicity_name

 ORDER BY upper(GROUP_CONCAT(ethnicity_name)) asc, upper(s.student_last_name) asc, upper(s.student_first_name) asc, upper(s.student_mi) asc

Produced these results:

Quote

<br />
<b>Fatal error</b>: Invalid use of group function using query: SELECT MIN(s.id) AS id, s.student_first_name, s.student_mi, s.student_last_name, s.major, s.gpa, s.school_year_other, s.application_date, s.modification_date, s.graduation, y.school_year_alt_display, cc.comments, IF(s.school_year_id = 0, s.school_year_other, y.school_year_alt_display) AS school_year_name, IF(s.student_ethnicity_interest_other IS NOT NULL AND s.student_ethnicity_interest_other != '', concat(s.student_ethnicity_interest_other, ', ', e.ethnicity_name), e.ethnicity_name) AS ethnicity_name FROM student s, completion_comments cc, ethnicity e, student_ethnicity_interest_assoc seia LEFT OUTER JOIN school_year y on s.school_year_id = y.id WHERE s.has_completion_reference_letter1 = cc.has_reference_letter1 AND s.has_completi
[/code]

Is this jsut something MySQL cannot do?

Phil