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

[Roland Bouman]

select s.first_name
, s.last_name
, group_concat(
e.ethnicity_name
order by e.ethnicity_name
)
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.first_name
, s.last_name
order by s.first_name
, s.last_name
, group_concat(
e.ethnicity_name
order by e.ethnicity_name
)

without the group by, you would get all combinations stored in student_ethnicity_interest_assoc, adorned with the related data in the 'lookup' tables student and ethnicity.
Now, the group by collects all the unique combinations in the group by expression list (in this case, student first name and last name). The group_concat function grabs the set of corresponding ethnicity_name occurrences and concatenates them in the order specified by the order by specified inside the group_concat function.

this is the initial result. The order by at the bottom of the statement sorts the initial result, yielding the final result.

I think the trouble you were experiencing with your query has to do with you're group by list. you see, all columns that appear in the select list must appear in the group by also, unless you have an aggregate function (like group_concat) over these columns.
Becuase you are grouping by only over id, whereas you should group by over the columns s.student_first_name, s.student_mi, s.student_last_name etc too.