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

Question

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

What is yearID ( a type of NUMBER or DATE) and add a little more detail about what HR is (COUNT/SOME OTHER)??? — Asif Mehmood, Mar 21 '16 at 20:15

ScaisEdge · Accepted Answer · 2016-03-21T20:27:26.497

1

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;

edited Mar 21 '16 at 20:27

answered Mar 21 '16 at 20:23

ScaisEdge

131,976
10
91
107

@MalikAsif .. please explain better why is not a good solution – ScaisEdge Mar 21 '16 at 20:27
1

Due to performance, it is not good ( look [HERE](http://stackoverflow.com/questions/36010981/group-only-certain-rows-with-group-by/36013983#36013983) , it is a same kind of answer and a way-way better solution) – Asif Mehmood Mar 21 '16 at 20:29
The question is not about performance.. and in the question there aren't info about dimensione of DB.. so i think this aspect is not related qith the question .. – ScaisEdge Mar 21 '16 at 20:51
1

Note that an uncorellated subquery will be faster by orders of magnitude – Strawberry Mar 21 '16 at 22:48

score 1 · Answer 2 · answered Mar 21 '16 at 21:37

As well as using a sub query you could also join the table to itself to get the desired result like so.

SELECT a.playerID, a.teamID, a.yearID as YEAR, a.HR AS MAXHR
FROM Batting a LEFT JOIN Batting b
ON a.yearID = b.yearID and a.HR < b.HR 
WHERE b.HR IS NULL;

This basically just joins the table to itself where the year is equal and the row from the left hand table has no higher run rate from the right hand (joined table).

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

2 Answers2