MySQL MAX shows wrong result for associated data

Question

I am creating a query to pull results from a MySQL database using PHP, the results of the query are output to an Excel document.

Here is the query:

$sql = "SELECT node, port, MAX(utiltx), MAX(utilrx), time, hour(time), day(time), month(time) FROM intfstats WHERE node IN('$separated') AND year(time)=$year AND month(time)=$month AND port IN('$portsep') GROUP BY node,port,day(time);";

I think the query works ok, but it always displays the time as midnight (which is first in the timestamp column).

The aim is for the product of the query to be the maximum utilisation rx and tx by day, and the time of day that the maximum throughput occured.

I would appreciate any help on this as I have spent far too long on it with not enough knowledge of MySQL queries to pull it off!!

Answer 1

How do you deal with tie-breakers, when you have 2+ records with the same max value on a given day?

SELECT t.node, 
       t.port, 
       t.utiltx, 
       t.utilrx, 
       t.time, 
       HOUR(t.time), 
       DAY(t.time), 
       MONTH(t.time) 
  FROM INTFSTATS t
  JOIN (SELECT is.node,
               is.port,
               MAX(is.utiltx) 'mutiltx', 
               MAX(is.utilrx) 'mutilrx',
               DAY(is.time) 'dd', 
               MONTH(is.time) 'mon',
               YEAR(is.time) 'yy'
          FROM INTFSTATS is
         WHERE is.node = $node
           AND is.port = $port
      GROUP BY is.node, 
               is.port, 
               DAY(is.time), 
               MONTH(is.time), 
               YEAR(is.time)) x ON x.node = t.node
                               AND x.port = t.port
                               AND x.mutiltx = t.utiltx
                               AND x.mutilrx = t.utilrx
                               AND x.dd = DAY(t.time)
                               AND x.mon = MONTH(t.time)
                               AND x.yy = YEAR(t.time)
 WHERE x.yy = $year
   AND x.mon = $mon