> Data in speed history looks like this: > interface_id updated_time speed > 1 2005-08-11 08:10:23 450112 > 1 2005-08-11 10:53:34 501120 <--- > 1 2005-08-11 10:58:11 450112 > 2 2005-08-11 08:10:23 450112 <--- > 2 2005-08-11 11:00:44 350234 > 3 2005-08-11 08:10:23 450112 <--- > The rows of speed_history I want back are marked above with ' <--- '. > > Query results should look like: > interface.interface_id > interface.link_description > speed_history.updated_time > speed_history.speed
The main idea is to join a select of max speeds grouped by interface_id to the interface table. If the query runs too slow, then you may get better performance having an index on (interface_id, speed) and using subselects. In this case you want to select information about all of interfaces and then have one of the columns be a subselect that selects one (using limit) speed from rows that have a matching interface_id ordered by interface_id desc, speed desc. This combination of limit and order by will be faster than using max. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
