Ordering a GROUP_CONCAT Column (MySQL)

Question

I was wondering if there was a way to order a GROUP_CONCAT-ed column by another column. Here's my example:

Tables

users

 id | name
----+------------
  0 | John Smith

users_majors

user_id | major_id | order
--------+----------+-------
   0    |    2     |  1
   0    |    1     |  0

majors

 id | name
----+-------------
  0 | Biology
  1 | Chemistry
  2 | Engineering

And here is my query:

SELECT `users`.*, GROUP_CONCAT(DISTINCT `majors`.name) AS major, GROUP_CONCAT(DISTINCT `users_majors`.order) AS major_order
FROM `users`
LEFT JOIN users_majors ON (users_majors.user_id = users.id)
LEFT JOIN majors ON (majors.id = users_majors.major_id)
GROUP BY users.id

I receive this result set, as expected:

id | name        | major                 | major_order
---+-------------+-----------------------+-------------
 0 | John Smith  | Engineering,Chemistry | 1,0

What I want to do is have the "major" column automatically be sorted by the "major_order" column, so I can remove that column and have the result set look like this:

id | name        | major
---+-------------+-----------------------
 0 | John Smith  | Chemistry,Engineering

Any suggestions?

score 2 · Accepted Answer · answered Aug 02 '11 at 15:11

GROUP_CONCAT includes an ORDER BY option.

SELECT `users`.*, GROUP_CONCAT(DISTINCT `majors`.name ORDER BY `users_majors`.order) AS major, GROUP_CONCAT(DISTINCT `users_majors`.order) AS major_order
FROM `users`
LEFT JOIN users_majors ON (users_majors.user_id = users.id)
LEFT JOIN majors ON (majors.id = users_majors.major_id)
GROUP BY users.id

Ordering a GROUP_CONCAT Column (MySQL)

1 Answers1