CODEWITHSUNDEEP
phpmysql
Sami Al-Subhi
Sami Al-Subhi

Reputation: 4662

finding the maximum value of a field of a MySQL query result

I have this Query : 

        select
        id_re_usr,
        year(time) as AYear,
        DAYOFYEAR(time) as ADay,
        DATE_FORMAT(time, "%m-%d-%y") as date,
        count(*) as TotalPerDay
        from numrequest
        where id_re_usr = "u1"
        group by id_re_usr, AYear, ADay
        order by AYear, ADay

it outputs something like 

   date      TotalPerDay 
------------------------        
01-01-87         1
01-09-12         5
02-09-12         17
03-09-12         1

how can I find the maximum TotalPerDay without changing the current output by using php or changing the query.

I tried to do this and it works 

  $max=0;
  while($row=mysql_fetch_array($results)){
      if($max<$row['TotalPerDay']){ $max= $row['TotalPerDay'];} 
  }

but isn't there a direct way to do it?

if modifying the query the output should be

   date      TotalPerDay        max
----------------------------------------        
01-01-87         1               17
01-09-12         5               17
02-09-12         17              17
03-09-12         1               17

Upvotes: 0

Views: 184

Answers (1)

DRapp
DRapp

Reputation: 48139

Join it to a second query of just the max count.. The inner-most queries on a per day basis (for the given user) a set of rows on count grouped per day. From that, the next outer does a select MAX() from that set to find and get only one record representing the highest day count... Since it will always return a single row, and joined to the original numRequest table it will be a Cartesian, but no problem since its only one record and you want that value on every returned row anyhow.

select
      id_re_usr,
      year(time) as AYear,
      DAYOFYEAR(time) as ADay,
      DATE_FORMAT(time, "%m-%d-%y") as date,
      count(*) as TotalPerDay,
      HighestCount.Max1 as HighestOneDayCount
   from
      numrequest,
      ( select max( CountsByDate.DayCount ) Max1
           from ( select count(*) as DayCount
                     from numrequests nr
                     where nr.id_re_usr = "u1"
                     group by date( nr.time )) CountsByDate
      ) HighestCount
   where 
      id_re_usr = "u1"
   group by 
      id_re_usr, 
      AYear, 
      ADay
   order by 
      AYear, 
      ADay

Upvotes: 1

Related Questions