0

Huge fan of the site, this is my first post. My question: I have a calculated column (BA for Batting Average) from a baseball stat database. I am trying to query the highest BA from a particular players career, which I know I need the MAX aggregated function. My desired query result should pull the highest batting average and the year in which the player achieved this. I've included two rows of a particular players stats from 1986, using this my desired query should be BA: .200 year:(1986).

Year    Team    Lg  G   PA  AB  R   H   2B  3B  HR  RBI SB  CS  BB  SO  BA  OBP 

1986    TOR     AL  3   5   5   1   1   0   0   0   0   0   0   0   2  .200 .200    
1987    TOR     AL  4   5   10  1   1   0   0   0   0   0   1   0   4  .100 .100

MySQL code is as follows:

SELECT MAX(TRIM(LEADING '0' FROM ROUND(H/AB,3))) as BA, yearID 
FROM mytable WHERE playerID = 'bathruth'

This code produces the correct max batting average but gives the first results in the yearID (The players rookie/debut year).

I would like the year (yearID) to be from the same row as the MAX BA (Highest Batting Average)

Michael
  • 1
  • 1
  • Ah yes, Bath Ruth. The Sultan of Slosh. The Great Bathtubo. – philipxy Mar 22 '17 at 01:46
  • 1
    Try [Groupwise maximum](http://stackoverflow.com/questions/15211479/groupwise-maximum) – Solarflare Mar 22 '17 at 02:12
  • I think the issue I'm having is that the BA column was not populated from the database, it's a calculated column, and thus MYSQL does not recognize this as a column. – Michael Mar 22 '17 at 02:17
  • 1
    The problem is not the calculated column. Just try it with a different column, you will get the same wrong result. The problem is your usage of `group by`: it will give you a random row for "year". That is the expected result. Have a look at the link in my comment. – Solarflare Mar 22 '17 at 19:45
  • Possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – philipxy Mar 24 '17 at 01:25
  • (Solarflare) I'm not using GROUP BY. The yearID is not random, based on the code provided it produces the first row of what is in the yearID column and the correct MAX for Batting Average. – Michael Mar 24 '17 at 16:21
  • Pardon my ignorance, I've looked at the Groupwise Maximum link you provided. I'm not sure what "T.1 and T.2" means. Does this mean Table 1, Table 2? – Michael Mar 24 '17 at 16:24

0 Answers0