quote:

---

Originally posted by jbon

Hi,
I got 3 tables, VehicleType (VehicleTypeID is PK), Vehicle (VehicleID isPK, VehicleTypeID is FK) and Position (PositionID is PK, VehicleID is FK). Position table save all positions from all vehicles. I want from that table have latest position for all vehicles, how do I get this? PositionID is just automatic numbered 1, 2, 3, ... I use (so far) following sql query but it gives me all positions, I want only latest position for all vehicles. Can I use DateTime for having latest positions in some way? Or am I forced to use PositionID to get my wanted result?

SELECT RegNo, AltID, VehicleType, DateTime, Speed, Latitude, Longitude From Vehicle v
JOIN VehicleType vt ON v.VehicleTypeID = vt.VehicleTypeID
Join Position p ON v.VehicleID = p.VehicleID
ORDER BY DateTime DESC

Thanks in advance!
/J

---

SELECT RegNo, AltID, VehicleType, DateTime, Speed, Latitude, Longitude 
From Vehicle v 
JOIN VehicleType vt 
ON v.VehicleTypeID = vt.VehicleTypeID
Join Position p ON v.VehicleID = p.VehicleID
Join (SELECT MAX(PositionID) AS MaxPos, VehicleID
      FROM Position
      GROUP BY VehicleID
     )tmp
ON tmp.MaxPos=p.PositionID
AND tmp.VehicleID=v.VehicleID