« Cas has a pants problem and Bono is an idiot. | Main | I uploaded some really old videos to youtube »
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:
| user_name | item_name |
|---|---|
| Will | comic book |
| Will | video game |
| Carl | hair gel |
And then I'd have to go through that data programatically to group my comic book and video game together.
But with 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:
| user_name | item_name |
|---|---|
| Will | comic book,video game |
| Carl | hair gel |
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:
| user_name | MAX(price) | item_name |
|---|---|---|
| Will | 50.00 | video game |
| Carl | 30.00 | hair gel |
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.
Posted by Will at March 12, 2009 11:22 PM
Comments
Man, Carl buys some expensive hair gel.
Posted by: sydney at March 17, 2009 10:50 AM
Sydney, you're a trooper for making it through this post.
Posted by: Will at March 17, 2009 12:33 PM
Awesome about the whole GROUP_CONCAT thing. So now that you've got that squared away, where are we on the "learning html" thing? Just askin'. :)
Posted by: Blue Eyed Greek at March 18, 2009 6:13 PM
