MySQL Forums
Forum List  »  InnoDB

Re: Sort (ORDER BY) by a comma-separated list of related fields using MySQL 4.1?
Posted by: Roland Bouman
Date: March 02, 2006 04:40PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Sort (ORDER BY) by a comma-separated list of related fields using MySQL 4.1?
3696
March 02, 2006 04:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.