I'm trying to learn MYSQL so I can conduct my own baseball research. I've been successful thus far, but what seems like a simple query is proving difficult and I can't seem to locate a solution.
I'm trying to write a query that spits our who hit the most HR in each year. My code is:
yearID AS Year,
MAX(HR) AS MaxHR
yearID, playerID, teamID
yearID DESC, MAXHR DESC;
You should use a subselect for getting the yearID and the HR
SELECT playerID, teamID, yearID AS Year, HR AS MaxHR FROM Batting WHERE (yearID, HR) in (select yearID, max(hr) from batting group by yearID) ORDER BY yearID DESC, MAXHR DESC;