SQL Query for returning highs for decades

Question

THIS IS TO USE MYSQL My question is the following. I have a baseball database, and this baseball database has a master table that lists all the players that have ever played. There is also a batting table that keeps track of each player’s batting statistics. I created a presentation to combine the two together; hence the masterplusbatting table. Now I want to find the highest HR striker in every decade since the beginning of baseball. Here is what I have 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 yearID to get 187, 188, 189 , etc. instead of 1897, 1885, Then I grouped by the decade, believing that this would give me a maximum for a decade, but did not return the correct values. For example, this gives me Adrian Beltra with 48 HR in 2004, but everyone knows that Barry Bonds got into 73 HR in 2001. Can someone give me some pointers?

Answer 1

 SELECT Lookup.DecadeID, Data.* FROM ( SELECT truncate(yearid/10,0) as decadeID, MAX(HR) as Homers FROM masterplusbatting GROUP BY truncate(yearid/10,0) ) AS lookup INNER JOIN masterplusbatting AS data ON data.yearid >= lookup.decadeID * 10 AND data.yearid < lookup.decadeID * 10 + 10 AND data.HR = lookup.homers 

Edited for MySQL

Answer 2

Your request seems to be aggregated for a different reason. Somewhere I expect to see a player based group.

 select truncate(mpb.yearid/10, 0) as decade, mpb.nameFirst, mpb.nameLast, sum(mpb.HR) as HRs from masterplusbatting mpb group by truncate(mpb.yearid/10, 0), mpb.nameFirst, mpb.nameLast 

This gives the total number of home launches for every decade. Now you want the person to be the most. For this, I would use the row_number() function and subqueries:

 select decade, nameFirst, nameLast, HRs from (select decade, nameFirst, nameLast, HRs, row_number() over (partition by decade order by HRs desc) as seqnum from (select truncate(mpb.yearid/10, 0) as decade, mpb.nameFirst, mpb.nameLast, sum(mpb.HR) as HRs from masterplusbatting mpb group by truncate(mpb.yearid/10, 0), mpb.nameFirst, mpb.nameLast ) t ) t where seqnum = 1 order by 1 

In a database like MySQL, you need to do this with a second connection:

 select t.* from (select truncate(mpb.yearid/10, 0) as decade, mpb.nameFirst, mpb.nameLast, sum(mpb.HR) as HRs from masterplusbatting mpb group by truncate(mpb.yearid/10, 0), mpb.nameFirst, mpb.nameLast ) t join (select decade, max(HRs) as maxHRs from (select truncate(mpb.yearid/10, 0) as decade, mpb.nameFirst, mpb.nameLast, sum(mpb.HR) as HRs from masterplusbatting mpb group by truncate(mpb.yearid/10, 0), mpb.nameFirst, mpb.nameLast ) t group by decade ) tsum on t.decade = tsum.decade and t.HRs = tsum.maxHRs order by decade