Thursday, December 2, 2010

MySql GROUP BY queries in older MYsql version

I decided to document this little situation I had recently with an SQL statement since its been a couple of times I encountered this kind of problem.

This may not be the best example but here it is
Heres the table:

Table: Deceased
Id        |         Name          |          Death           |
1         |         Franz           |          1989            |
2         |         Franz           |          1700            |
3         |         Franz           |          2010            |
5         |         John             |          2006            |
6         |         John             |          1980            |


Requirement: We want to query the person in a group with the same name who died the most recent.
So if we were to query the table above we want to get the result:
row: Id: 3, Franz, 2010
row: Id: 5: John, 2006

So how would we do this? There maybe many solutions out there but this is the sql I thought of out of hand:
SELECT *, MAX(Death) as Recent  FROM Deceased
GROUP BY Name HAVING Death IN (Recent)
This SQL above may work if you have MYsql's ONLY_FULL_GROUP_BY mode disabled but will give you headaches if it is on or if you have an older version as of mysql (<= MySQL 5.0.23). Learned that the hard way. :D

So heres my solution if you have only_full_group_by mode turned on:
SELECT * FROM Deceased
GROUP BY Name, Death HAVING Death IN (SELECT MAX(Death)  FROM Deceased
GROUP BY Name)

OR

SELECT * FROM Deceased WHERE
Id IN (SELECT IF(MAX(Death), Id, null)  FROM Deceased
GROUP BY Name)

Yep a subquery and yep it may be slow. A little explanation on the last query. I used Id with a sub query because I realize that if I used the death column, there may be people who died on the same year that is not the result of a MAX therefore those years are not unique so we have to compare against the ID. One catch is a subquery needs to have 1 column so we have to use an IF().

10 comments:

Coach Factory said...

moncler outlet, cheap oakley sunglasses, canada goose outlet, oakley outlet, ugg boots, ray ban outlet, oakley sunglasses cheap, ray ban, ray ban outlet, ray-ban, oakley sunglasses outlet, oakley outlet, cheap oakley sunglasses, oakley vault, ray ban sunglasses outlet, canada goose, oakley vault, louboutin, tn pas cher, cheap oakley sunglasses, ray ban, canada goose, ray ban, ugg, oakley sunglasses outlet, ray bans, raybans, canada goose, oakley vault, ray-ban, oakley outlet, cheap sunglasses, ralph lauren, rayban, cheap oakley, moncler, oakley sunglasses cheap, gucci, oakley sunglasses outlet, moncler, oakley, canada goose, cheap sunglasses, ray bans, cheap sunglasses, oakley sunglasses cheap, moncler, canada goose outlet, rayban, discount oakley sunglasses

Coach Factory said...

jordan shoes, louboutin, michael kors outlet, michael kors, michael kors, louis vuitton handbags, nike air max, louis vuitton, gucci outlet, true religion outlet, michael kors outlet, air max, coach outlet store, ray ban sunglasses, burberry outlet online, michael kors, longchamp outlet, nike free, true religion jeans, burberry outlet online, coach factory outlet, louboutin, toms shoes, air jordan, polo ralph lauren, chanel handbags, ray ban, prada outlet, tiffany and co, nike air max, polo ralph lauren outlet, louboutin, coach outlet, nike shoes, ralph lauren, kate spade outlet, tory burch outlet, air max, oakley sunglasses, oakley sunglasses, true religion, michael kors outlet, oakley sunglasses, longchamp, prada handbags, nike free, coach purses, coach factory,

Coach Factory said...

celine handbags, chi flat iron, nike air max, nfl jerseys, ugg boots, lululemon, mcm handbags, marc jacobs, supra shoes, soccer shoes, lancel, beats by dre, vans, p90x3, ghd, herve leger, nike free, ferragamo shoes, burberry, birkin bag, juicy couture outlet, montre pas cher, soccer jerseys, ralph lauren, hermes, north face outlet, ugg boots, valentino shoes, louis vuitton, jimmy choo, new balance shoes, mulberry, rolex watches, louis vuitton, abercrombie and fitch, oakley pas cher, reebok outlet, hollister, air max, mont blanc, bottega veneta, converse shoes, ray ban pas cher, karen millen, wedding dresses, hogan, yoga pants, insanity, asics gel, timberland

co wendy said...

Sergei , October 22, 1970 Born in Christian Louboutin Bois Dore Moscow State Ozherelye. Perhaps influenced by Cheap LV Handbags their parents worship warrior army, join the army in cheap jordans 1989, and into the elite airborne military service. 1991, by the influence of the Soviet Union, the uggs on sale Russian christian louboutin army general lack of funds, a large number ugg australia of Air Jordan 11 Gamma Blue soldiers seeking veterans, but under extremely difficult conditions still dedicated, praise superiors, and soon was sent to the famous christian louboutin shoes Ryazan Higher ugg Airborne Command School studies. After graduating in 1994, entered the famous Pskov 76th Guards Airborne discount christian louboutin Division, served as discount nike jordans the reconnaissance platoon, reconnaissance ugg soldes deputy company commander, company commander, battalion airborne regiment scouting director and other duties.From 2000 to Cheap Louis Vuitton Handbags 2004, where the forces have repeatedly ordered war with Chechen militants. Because of their opponents in order to form the squad activity and haunted impermanence, the Russians had to ugg boots mobilize elite troops set up large uggs outlet number of small units, "a Discount Louis Vuitton small wholesale jordan shoes play small" Implementation siege. commanding troops in Chechnya, Christian Louboutin Daffodile Ingushetia and other places over the mountains, to track the militants fled. Discount LV Handbags Although the militants were extremely vigilant, but as long as the task has never had outsmarted.August 8, 2008, Georgia suddenly invade South Ossetia (Russian peacekeepers stationed ugg pas cher there), the Russian military to respond quickly, including including the 76th Guards Airborne Division, more than christian louboutin remise 50% 3,000 people were immediately delivered to the pro-Russian Abkhazia, Georgia cheap nike jordan shoes Army contain two infantry brigades, effectively coordinate the cheap christian louboutin direction of the Russian Bags Louis Vuitton troops in South Ossetia.

Unknown said...

thomas sabo uk
herve leger outlet
coach outlet online
louis vuitton handbags
rolex watches
ugg boots
iphone case
cheap snapbacks
uggs outlet
michael kors outlet online
cartier outlet
michael kors outlet clearance
soccer jerseys
oakley sunglasses
celine outlet
canada goose outlet
coach outlet
futbol baratas
nike air force 1
longchamp outlet
coach outlet
michael kors outlet
ugg outlet
canada goose jackets
hermes birkin
polo ralph lauren
michael kors outlet store
air jordan 4
cheap uggs
tory burch outlet
tiffany and co jewelry
louis vuitton outlet
coach outlet
michael kors bags
ugg boots
chanyuan0913

roshini said...


Sage Support Phone Number
Sage 50 Tech Support Phone Number
Sage 50 Support Number
Sage 50 Technical Support Phone Number
Sage 50 Tech Support Number
Sage 50 Technical Support Phone Number
Sage 50 Technical Support Number
Sage 50 Customer Support Phone Number
Sage 50 Customer Service Number

Soni yadav said...

Aol Technical Support Phone Number
Aol Technical Support Number
Aol Tech Support Number
Aol Tech Support phone Number
Aol Support Phone Number
Aol Tech Support Number
Outlook Technical Support Phone Number
Outlook Technical Support Number
Outlook Tech Support Number
Outlook Tech Support Phone Number
Outlook Support Phone Number
Outlook Tech Support Number

Anjilo Jilo said...


Quicken Support Phone Number
Quicken Support Phone Number
Quicken Support Number
Quicken Tech Support Number
Quicken Tech Support Phone Number
Quicken Technical Support Number
Quicken Technical Support Phone Number
Quicken Technical Support Number
Quicken Tech Support Phone Number
Quicken Tech Support Number

mohan kumar said...

Quickbooks Support Phone Number
Quickbooks Pro Support Phone Number
Quickbooks Pro Tech Support Number
Quickbooks Pro Support Number
Quickbooks Pro Technical Support Number
Quickbooks Pro Tech Support Phone Number
Quickbooks Pro Technical Support Phone Number
Quickbooks Pro Help Desk Phone Number
Quickbooks Pro Help Desk Number
Quickbooks Pro Customer Service Number
Quickbooks Pro Customer Service Phone Number
Quickbooks Pro Customer Support Number
Quickbooks Pro Customer Support Phone Number

Unknown said...

birkenstock sandals
cheap oakley sunglasses
michael kors handbags
raiders jerseys
coach handbags
carolina panthers jersey
louis vuitton sacs
new york jets jerseys
nike huarache
arizona cardinals jerseys