CODEWITHSUNDEEP
mysqlsubquerymax
David Eno
David Eno

Reputation: 147

MySQL - max( ) row values of subquery

I have a table in which the rainfall is recorded every 15 minutes, so the total rainfall for a day is the sum of 'rain' in 95 records. The following query correctly returns a list of dates and the rainfall for that day. 

select thedate as tdate,sum(rain) as dailyrain
from  weatherdata
group by year(thedate), month(thedate), day(thedate)

(thedate stored as datetime, rain stored as integer )

I now want to make this a sub query and return the date on which there was maximum rainfall.

My query below correctly returns the maximum rain amount, but not the correct date linked with that rainfall.

select maxdate,max(dailyrain) from (select thedate as maxdate,sum(rain) as dailyrain
from  weatherdata
group by year(thedate), month(thedate), day(thedate)) as maxrain

I think I probably have to use a JOIN. I know many similar questions have been answered here but I can't quite get the syntax right to make this work. Any help would be much appreciated.

Upvotes: 0

Views: 846

Answers (3)

eggyal
eggyal

Reputation: 126025

To get all dates having the maximal rainfall:

SELECT   DATE(thedate), SUM(rain)
FROM     weatherdata
GROUP BY DATE(thedate)
HAVING   SUM(rain) = (
           SELECT   SUM(rain)
           FROM     weatherdata
           GROUP BY DATE(thedate)
           ORDER BY SUM(rain) DESC
           LIMIT    1
         )

Upvotes: 1

Jean-Bernard Pellerin
Jean-Bernard Pellerin

Reputation: 12680

SELECT DATE(thedate) as tdate, sum(rain) as dailyrain
FROM weatherdata
GROUP BY DATE(thedate)
ORDER BY sum(rain) DESC, DATE(thedate) DESC
LIMIT 1 ;

or:

SELECT DATE(thedate) as tdate, sum(rain) as dailyrain
FROM weatherdata
GROUP BY DATE(thedate)
ORDER BY dailyrain DESC, tdate DESC
LIMIT 1 ;

Upvotes: 2

Tin Tran
Tin Tran

Reputation: 6202

please try this

select tdate,dailyrain from
   (select date(thedate) as tdate,sum(rain) as dailyrain
    from  weatherdata
    group by date(thedate))r
WHERE dailyrain =
                  (select max(dailyrain) as maxrain from
                      (select date(thedate) as tdate,sum(rain) as dailyrain
                       from  weatherdata
                       group by date(thedate)
                      )m
                   )

sqlFiddle

Upvotes: 0

Related Questions