Comparing Max Value with Second Highest Value Among Players?

[Chuck Hildebrandt]

I want to do a query in which, among all baseball players who've had 10 or
more seasons of 500 or more at bats, I compare each player's maximum home
run season with their second highest home run season, to see which players
have the greatest "outlier" seasons (i.e., the greatest difference between
their highest HR season and their second highest HR season).

With a prep query, I've whittled my total DB down to the list of 133 players
who've had 10 or more 500+ at bat seasons. How can I now extract (or
excise) the maximum home run season for each player from this list, and then
find the maximum home run season for each player's remaining season?

I think if I can find out how to do that, I can take the query home. Any
ideas?

Thank you.

Chuck

 

[KARL DEWEY]

First I would run a query just to see if any had the same for any two
seasons.
These you need to jot down for reference later.
Run a totals query for maximum season for each player. Use that query in a
second query for maximum with criteria as not same value as in the first
query.
Then use the two queries in a third to list both the highest and second
highest.