Reputation: 49
I am working on a table and I have a problem with a statement. I have a table which contains MLB 2018 season results. One column of this table is "TotalRuns" and describes the amount of runs for each game. My problem is that I can't find which day of MLB 2018 season has the maximum amount of runs.My certain idea is to write this:
This actually returns the maximum of TotalRuns per Day but I want to see which day it happened.
*I know the other way with LIMIT statement but this will not work if 2 or more days have the requirement maximum value *.
Any ideas?
SELECT MAX(Runs) as MaximumRunsPerDay
FROM (SELECT Date, SUM(TotalRuns) as Runs FROM mlb
GROUP BY date) as m;
Upvotes: 1
Views: 46
Reputation: 133400
You need some subquery and join
select date, runs
from (
SELECT Date, SUM(TotalRuns) as Runs
FROM mlb
GROUP BY date
) t0 inner join (
select max(Runs) max_runs
from (
SELECT Date, SUM(TotalRuns) as Runs
FROM mlb
GROUP BY date
)t1
) t2 on t2.max_runs = t0.runs
Upvotes: 1