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

[Roland Bouman]

Whoops, my bad - i guess i was a bit sleepy when i gave you that answer.

I meant:

select s.student_first_name
, s.student_last_name
, group_concat(
e.ethnicity_name
order by e.ethnicity_name
) as etnicities
from student s
inner join student_ethnicity_interest_assoc s2e
on s.id = s2e.student_id
inner join ethnicity e
on s2e.ethnicity_id = e.id
group by s.student_first_name
, s.student_last_name
order by s.student_first_name
, s.student_last_name
, etnicities

Alternatively, you can try if it works if you write it like this:

select student_first_name
, student_last_name
, ethnicities
from (
select s.student_first_name
, s.student_last_name
, group_concat(
e.ethnicity_name
order by e.ethnicity_name
) as etnicities
from student s
inner join student_ethnicity_interest_assoc s2e
on s.id = s2e.student_id
inner join ethnicity e
on s2e.ethnicity_id = e.id
group by s.student_first_name
, s.student_last_name
)
order by student_first_name
, student_last_name
, etnicities