« 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_nameitem_name
Willcomic book
Willvideo game
Carlhair 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_nameitem_name
Willcomic book,video game
Carlhair 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_nameMAX(price)item_name
Will50.00video game
Carl30.00hair 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