user3285817 user3285817 - 3 months ago 7
MySQL Question

MySQL, Determine value associated with MAX() of another value using GROUP BY

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;

Answer

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
                      );
Comments