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().

4 comments:

Unknown 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

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

caiyan said...

polo ralph lauren outle
ray ban sunglasses cheap
adidas nmd r1
michael kors outlet store
rolex replica watches for sale
michael kors uk
pandora jewellery
fit flops
kate spade
ralph lauren uk
0413shizhong