1

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?

| improve this question | |
3

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 ;
| improve this answer | |
  • 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. – Raymond Nijland Aug 17 '13 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.