Hello, my name is: Amy

MySQL GROUP_CONCAT

I ran into GROUP_CONCAT in some pre-existing code and had a sneaking suspicion something wasn't working properly (though I had no clue what GROUP_CONCAT even was). I knew that that I needed the output to be in a specific order, but the query didn't seem to be listening to the ORDER BY that was tagged on to the end.

A few google searches later, turns out:

GROUP_CONCAT returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.

Clauses can be added to the results, and ORDER BY was indeed one of those options. I just needed to use it inside the parenthesis of the GROUP_CONCAT rather than tagged on at the end. Also available: DISTINCT (prevent duplicates), and SEPARATOR (Choose how you want the variables to be delimited. The default is ,).

Here's how the query looks all dressed up as if I was trying to get an alphabetical list of the unique countries within a customer table.

GROUP_CONCAT(DISTINCT country
    ORDER BY country
    SEPARATOR ';');

I am pretty clear with my understanding this straightforward country example. I might have to think a little harder to use this for other applications depending on how the database is set up. But at least I now know that this exists.

Best Sources I've found on this:

Comments