March 12, 2009
GROUP_CONCAT is the most awesome MySQL function ever
It's been a while since I've filed a post under the 'boring tech stuff' category, and I discovered something today that I found very exciting. For those of you who don't know what MySQL is, you can stop reading now. This will be very boring and won't make any sense.
Now, let's say you have an online store. You have a tabled called
user and one called
purchase that holds the data for every purchase a user makes (including stuff about the item they purchased, which would usually be in a different table, but for the sake of simplicity, we'll say it's in
purchase). Now let's say you want to get a list of all your users and all the purchases they've made. Until I found out about
GROUP_CONCAT, I would have done something like this:
SELECT user.user_name, purchase.item_name FROM user, purchase WHERE purchase.user_id=user.user_id
which would return something like this:
And then I'd have to go through that data programatically to group my comic book and video game together.
GROUP_CONCAT, I can do this:
SELECT user.user_name, GROUP_CONCAT(purchase.item_name) FROM user, purchase WHERE purchase.user_id=user.user_id GROUP BY user.user_id
Which gives me:
|Will||comic book,video game|
Now let's say you want to get a list of all your users and the most expensive thing they've ever bought. You'd think this would work:
SELECT user.user_name, MAX(purchase.price), purchase.item_name FROM user, purchase WHERE purchase.user_id=user.user_id GROUP BY user.user_id ORDER by purchase.price DESC
But it doesn't. It'll give you the maximum price, but the item_name will be whatever MySQL feels like giving from the purchase list, not necessarily the one that goes along with the
MAX(price), even with that
ORDER BY at the end. Here's where
GROUP_CONCAT comes in, using its optional
ORDER BY clause.
SELECT user.user_name, MAX(purchase.price), SUBSTRING_INDEX(GROUP_CONCAT(purchase.item_name ORDER BY purchase.price DESC), ',', 1) FROM user, purchase WHERE purchase.user_id=user.user_id GROUP BY user.user_id
That's a bit of a mess, so let's look at the important part:
GROUP_CONCAT(purchase.item_name ORDER BY purchase.price DESC)
That gives the list of purchases for the user, ordered by price descending, and since we're only interested in the first entry in that list, the most expensive one, we slap around it:
SUBSTRING_INDEX(GROUP_CONCAT..., ',', 1)
The two arguments after the GROUP_CONCAT are to tell it to look for the first comma it sees and return whatever's before it. So you end up with:
Like I said before, you can do all this stuff programatically, but if you hate leaving your SQL prompt, this comes in handy.
Apologies to my non-techie readership. I'll come up with some other way to bore you next time.