0

I have a MySQL database that contains the table, "message_route". This table tracks the the path between hubs a message from a device takes before it finds a modem and goes out to the internet.

"message_route" contains the following columns:

id, summary_id, device_id, hub_address, hop_count, event_time

Each row in the table represents a single "hop" between two hubs. The column "device_id" gives the id of the device the message originated from. The column "hub_address" gives the id of the hub the message hop was received by, and "hop_count" counts these hops incrementally. The full route of the message is bound together by the "summary_id" key. A snippet of the table to illustrate:

+-----+------------+-----------+-------------+-----------+---------------------+
| id  | summary_id | device_id | hub_address | hop_count | event_time          |
+-----+------------+-----------+-------------+-----------+---------------------+
| 180 |        158 |      1099 |       31527 |         1 | 2011-10-01 04:50:53 |
| 181 |        159 |      1676 |       51778 |         1 | 2011-10-01 00:12:04 |
| 182 |        159 |      1676 |       43567 |         2 | 2011-10-01 00:12:04 |
| 183 |        159 |      1676 |       33805 |         3 | 2011-10-01 00:12:04 |
| 184 |        160 |      2326 |       37575 |         1 | 2011-10-01 00:12:07 |
| 185 |        160 |      2326 |       48024 |         2 | 2011-10-01 00:12:07 |
| 186 |        160 |      2326 |       57652 |         3 | 2011-10-01 00:12:07 |
+-----+------------+-----------+-------------+-----------+---------------------+

There are three total messages here. The message with summary_id = 158 touched only one hub before finding a modem, so row with id = 180 is the entire record of that message. Summary_ids 159 and 160 each have 3 hops, each touching 3 different hubs. There is no upward limit of the number of hops a message can have.

I need to create a MySQL query that gives me a list of the unique "hub_address" values that constitute the last hop of a message. In other words, the hub_address associated with the maximum hop_count for each summary_id. With the database snippet above, the output should be "31527, 33805, 57652".

I have been unable to figure this out. In the meantime, I am using this code as a proxy, which only gives me the unique hub_address values for messages with a single hop, such as summary_id = 158.

SELECT DISTINCT(x.hub_address)
FROM (SELECT hub_address, COUNT(summary_id) AS freq  
    FROM message_route GROUP BY summary_id) AS x
WHERE x.freq = 1;
Fabian Sierra
  • 766
  • 1
  • 8
  • 22
OnlyDean
  • 1,025
  • 1
  • 13
  • 25

1 Answers1

2

I would approach this as:

select distinct mr.hub_address
from message_route mr
where mr.event_time = (select max(mr2.event_time)
                       from message_route mr2
                       where mr2.summary_id = mr.summary_id
                      );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This seems to do the trick! Thank you for helping me break out of the rut where I assumed "GROUP BY" was necessary to solve the problem. – OnlyDean Aug 25 '16 at 20:37