SQL Query Get Max Value At Occured Date

Question

So we are doing some traffic reporting in our department. Therefore we got a table named traffic_report, which is build up like

╔════════════════╦═══════════╦═════════════════════╦═════════════╦═════════════╗
║    hostname    ║ interface ║      date_gmt       ║ intraf_mpbs ║ outraf_mbps ║
╠════════════════╬═══════════╬═════════════════════╬═════════════╬═════════════╣
║ my-machine.com ║ NIC-5     ║ 2013-09-18 09:55:00 ║          32 ║          22 ║
║ my-machine.com ║ NIC-5     ║ 2013-09-17 08:25:00 ║          55 ║          72 ║
║ my-machine.com ║ NIC-5     ║ 2013-09-16 05:12:00 ║          65 ║           2 ║
║ my-machine.com ║ NIC-5     ║ 2013-09-15 04:46:00 ║          43 ║           5 ║
║ my-machine.com ║ NIC-5     ║ 2013-09-14 12:02:00 ║          22 ║          21 ║
║ my-machine.com ║ NIC-5     ║ 2013-09-13 22:13:00 ║          66 ║          64 ║
╚════════════════╩═══════════╩═════════════════════╩═════════════╩═════════════╝

I'd like to fetch the maximum of the traffic in and traffic out at the occured date. My approach doing so is like this

SELECT hostname, interface, date_gmt, max(intraf_mbps) as max_in, max(outtraf_mbps) as max_out
FROM traffic_report
GROUP by hostname, interface

The approach produces a table like this

╔════════════════╦════════════╦═════════════════════╦════════╦═════════╗
║    hostname    ║ interface  ║      date_gmt       ║ max_in ║ max_out ║
╠════════════════╬════════════╬═════════════════════╬════════╬═════════╣
║ my-machine.com ║ NIC-5      ║ 2013-09-18 09:55:00 ║     66 ║      72 ║
╚════════════════╩════════════╩═════════════════════╩════════╩═════════╝

The problem is, the date_gmt displayed is just the date of the first record entered to the table.

How do I instruct SQL to display me the date_gmt at which the max(intraf_mbps) occured?

Answer 1

Your issue is with mysql hidden fields:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group.

Mysql has not rank features either analytic functions, to get your results, a readable approach but with very poor performance is:

SELECT hostname, 
       interface, 
       date_gmt, 
       intraf_mbps, 
       outtraf_mbps
FROM traffic_report T
where intraf_mbps + outtraf_mbps =
      ( select 
           max(intraf_mbps + outtraf_mbps) 
        FROM traffic_report T2
        WHERE T2.hostname = T.hostname and
              T2.interface = T.interface 
        GROUP by hostname, interface
      )

Sure you can work for a solution with more index friendly approach or avoid correlated subquery.

Notice than I have added both rates, in and out. Adapt solution to your needs.

Answer 2

Either of these approaches should work:

This first query returns the rows that match the maximum out and in values, so multiple rows can be returned if many records share the max or min values.

SELECT * from traffic_report 
WHERE intraf_mpbs = (SELECT MAX(intraf_mpbs) FROM traffic_report) 
   or outraf_mpbs = (SELECT MAX(outraf_mpbs) FROM traffic_report)

This second query returns more of a MI style result, add other fields if you require them.

SELECT "MAX IN TRAFFIC" AS stat_label,date_gmt AS stat_date, traffic_report.intraf_mpbs
  FROM traffic_report,(select MAX(intraf_mpbs) AS max_traf FROM traffic_report) as max_in
 WHERE traffic_report.intraf_mpbs = max_in.max_traf
 UNION
SELECT "MAX OUT TRAFFIC" AS stat_label,date_gmt AS stat_date, traffic_report.outraf_mpbs
  FROM traffic_report,(SELECT MAX(outraf_mpbs) AS max_traf FROM traffic_report) AS max_out
 WHERE traffic_report.outraf_mpbs = max_out.max_traf

Hope this helps.