Get GPS data (lat, lon, address) for each truckId with the latest timedate

I have the following table :

main(rowId, posDatetime, truckId, lat, lon, address)

rowId: PK
posDatetime: the date and time of the coordinates and address
truckId
lat,lon
address: address string

rows are not fed in datetime order.

to get the latest position for each truck I hit the DB with this query FOR EACH truck:

SELECT * FROM main WHERE truckId=XXXX ORDER BY posDatetime DESC LIMIT 1

It gets me what I want but I feel it is inefficient, ive tried using MAX() but it gets me rows of data that are not the one with the MAX() value.

Is there a way to do something like:

select * from main where rowId=MAX(posDatetime).rowID GROUP BY truckId

Is there anything like it?

Answer

A solution that will quickly always give a trucks last stated position. An alternative solution would be to add the two columns posDateTime and mainId to the truck table and change the logic in the trigger.

The solution assumes you have a version of mysql that supports triggers.

-- drop table if exists last_position_for_truck ;

CREATE TABLE if not exists `last_position_for_truck` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `truckId` int(11) unsigned NOT NULL,
  `mainId` int(11) unsigned NOT NULL,
  `posDateTime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `truckId` (`truckId`)
) ENGINE=myisam DEFAULT CHARSET=latin1;

-- make sure there is a one to one relationship with trucks and last_position_for_truck
-- can be run multiple times
-- must be run if a new truck is added if nothing else is done to maintain the one to one relationship
insert into `last_position_for_truck` ( truckId, mainId, posDateTime )
select truckId, mainId, posDateTime from (
    select truck.id truckId, 0 mainId,  DATE_SUB(NOW(), INTERVAL 1 YEAR) `posDateTime`
    , last_position_for_truck.id last_position_for_truck_id
    from truck
    left join last_position_for_truck
    on last_position_for_truck.truckId = truck.id
) last_position_for_truck
where last_position_for_truck_id is null ;

-- DROP TRIGGER if exists main_insert_trigger ;

delimiter $$
CREATE TRIGGER main_insert_trigger AFTER insert ON main
  FOR EACH ROW BEGIN
    update last_position_for_truck 
    set posDateTime = new.posDateTime 
    , mainId = new.id
    where truckId = NEW.truckId and posDateTime < NEW.posDateTime ;
  END$$
delimiter ;

-- assume there is a truck id of 1 and 2, -35.8739731, 152.22774 is somewhere in Asutralia
insert into main( truckId, posDateTime, lat, lon, address ) values ( 2, now(), -35.8739731, 152.22774, 'Somewhere in Australia' ) ;
insert into main( truckId, posDateTime, lat, lon, address ) values ( 1, now(), -35.8739731, 152.22774, 'Somewhere in Australia' ) ;

-- see the results
select last_position_for_truck.truckId 
, last_position_for_truck.mainId
, main.lat
, main.lon
, main.`posDateTime`
from last_position_for_truck 
left join main
on main.id = last_position_for_truck.mainId
where last_position_for_truck.id in (1,2) ;

-- sample results
1   14  -35.874 152.228 2013-01-15 11:00:18
2   13  -35.874 152.228 2013-01-15 10:59:33