CODEWITHSUNDEEP
mysqlgroup-bymax
Apantazo
Apantazo

Reputation: 49

How to extract row with max value after group by statement?

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

Answers (1)

ScaisEdge
ScaisEdge

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

Related Questions