How to combine two rows based on timestamps

Question

So i know this is an odd way of doing it but ill try and explain.

I have the following table trackingPoint{id, moduleId, lat, long, timestamp, first, last}

I now have the problem where points might arrive out of order. The php that handles the entry generates a trip record but it relies on order. I could adapt this script but im looking for a more efficient solution. I figure that if i do it all in a single query generated from the trackingPoints i eliminate some state, which cant be bad :D

The first tracking point in a trip is flagged with first=1 The last tracking point in a trip is flagged with last=1

So i need all rows where first=1, then i need to merge in the following row with last=1 To produce a row such as: startTimestamp, endTimestamp, startLat, startLong, endLat, endLong, moduleId

Answer 1

The easiest way to do this is to add a TripID column to your table, which you can trivially group or filter on. eg.

select * from trackingPoint where TripID = ?;
select tripID, min(ts), max(ts) from trackingPoint where TripID = ?;