SQL Query to return maximums over decades
- by Abraham Lincoln
My question is the following. I have a baseball database, and in that baseball database there is a master table which lists every player that has ever played. There is also a batting table, which tracks every players' batting statistics. I created a view to join those two together; hence the masterplusbatting table.
CREATE TABLE `Master` (
`lahmanID` int(9) NOT NULL auto_increment,
`playerID` varchar(10) NOT NULL default '',
`nameFirst` varchar(50) default NULL,
`nameLast` varchar(50) NOT NULL default '',
PRIMARY KEY (`lahmanID`),
KEY `playerID` (`playerID`),
) ENGINE=MyISAM AUTO_INCREMENT=18968 DEFAULT CHARSET=latin1;
CREATE TABLE `Batting` (
`playerID` varchar(9) NOT NULL default '',
`yearID` smallint(4) unsigned NOT NULL default '0',
`teamID` char(3) NOT NULL default '',
`lgID` char(2) NOT NULL default '',
`HR` smallint(3) unsigned default NULL,
PRIMARY KEY (`playerID`,`yearID`,`stint`),
KEY `playerID` (`playerID`),
KEY `team` (`teamID`,`yearID`,`lgID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Anyway, my first query involved finding the most home runs hit every year since baseball began, including ties. The query to do that is the following....
select f.yearID, f.nameFirst, f.nameLast, f.HR
from (
select yearID, max(HR) as HOMERS
from masterplusbatting group by yearID
)as x inner join masterplusbatting as f on f.yearID = x.yearId and f.HR = x.HOMERS
This worked great. However, I now want to find the highest HR hitter in each decade since baseball began. Here is what I tried.
select f.yearID, truncate(f.yearid/10,0) as decade,f.nameFirst, f.nameLast, f.HR
from (
select yearID, max(HR) as HOMERS
from masterplusbatting group by yearID
)as x inner join masterplusbatting as f on f.yearID = x.yearId and f.HR = x.HOMERS
group by decade
You can see that I truncated the yearID in order to get 187, 188, 189 etc instead of 1897, 1885,. I then grouped by the decade, thinking that it would give me the highest per decade, but it is not returning the correct values. For example, it's giving me Adrian Beltre with 48 HR's in 2004 but everyone knows that Barry Bonds hit 73 HR in 2001. Can anyone give me some pointers?
