Mac Mac - 2 months ago 9
MySQL Question

MYSQL Beginner ORDER By - Only want MAX value for each entry in column

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:

SELECT
playerID, teamID,
yearID AS Year,
MAX(HR) AS MaxHR
FROM
Batting
GROUP BY
yearID, playerID, teamID
ORDER BY
yearID DESC, MAXHR DESC;


This comes close to what I want, but gives me every player chronologically by year instead of just #1 and then the next year. I'm sure I'm missing something obvious.

Thanks for the help

Answer

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;
Comments