MySQL Forums
Forum List  »  Newbie

Re: Finding min é max values for each day
Posted by: Peter Brawley
Date: November 26, 2017 07:15PM

One way: collect daily minima & maxima, join back to data table to find times of minima and maxima

select mm.d, mm.mintemp, time(mins.date) as mintime, mm.maxtemp, time(maxs.date) as maxtime
from (
  select date(date) as d, min(temp) as mintemp, max(temp) as maxtemp
  from tbl
  group by d
) mm
join tbl as mins on mm.d=date(maxs.date) and mm.mintemp=mins.temp   -- JOIN TO FIND TIME(S) OF DAILY MIN TEMP
join tbl as maxs on mm.d=date(mins.date) and mm.maxtemp=maxs.temp   -- JOIN TO FIND TIME(S) OF DAILY MAX TEMP

If a day's min or max occurs more than once, you'll need to decide what the query should do with such multiples.

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.