CODEWITHSUNDEEP
sql
John Seed
John Seed

Reputation: 387

SQL query to get max homeruns for each year

In the database we have the table batter, which contains a record for each batter each year they played as well as their game stats for that year. How would I write a query to select each player that has the max number of homeruns out of all players?

I've been trying with

SELECT *
FROM master
WHERE batting.HR = (
    SELECT MAX(batting.HR)
    FROM batting
)

But have had no luck. How could I do this?

Upvotes: 0

Views: 582

Answers (2)

Mr. Llama
Mr. Llama

Reputation: 20899

Assuming your RDBMS supports SQL2003, you can use a window function:

SELECT *
FROM
(
    SELECT
        batter,
        year,
        hr,
        MAX(hr) OVER (PARTITION BY year) AS year_best_hr
    FROM batting
)
WHERE hr = year_best_hr

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415901

SELECT b.* 
FROM batter b
INNER JOIN (
   select year, Max(hr) as MaxHR
   from batter
    group by year
) y on b.year = y.year and b.HR = y.MaxHR

Note this is vulnerable to two players tying. It's not clear what you want to see in that case.

Upvotes: 3

Related Questions