MySQL Grouping Order

Question

Is it possible to force the order in which a result set is grouped? I have a table with multiple languages in it, and I'm doing a GROUP_CONCAT to get a comma separated list of the product name in each language, for each product_id. However it doesn't seem possible to get MySQL to return that concatenated string in any particular order of language_id's. What I'd like is to be able to order the grouping by language id, so that they'll always come out in a pre-determined order.

Is this possible? If so, how? If not possible within the SELECT statement, is there a modification I can make to the table to adjust how the GROUP BY would order the result?

Answer 1

You can use ORDER BY inside GROUP_CONCAT() function. You can also change the separator, if you don't want to use comma. Further details in MySQL documentation: GROUP_CONCAT()

Example:

SELECT 
 product_id,
 GROUP_CONCAT(name 
 ORDER BY language_id
 SEPARATOR ','
 ) AS product_names
FROM
 products_languages
GROUP BY
 product_id ;

• Raymond Nijland: Note that GROUP_CONCAT has a limit of 1024 bytes. In latin1 encoding that means the return string should have 1024 chars in utf8 encoding you need to divide by three (1024/3) = 341 chars. You can use SET [GLOBAL | SESSION] group_concat_max_len = val; so it can have more chars.