MySQL query

[bigred]

Hi,

I have a MySQL table containing GPS waypoints of a running session, with each waypoint containing a routeID, longitude, latitude, altitude and time. The time is stored in seconds since the start of the run.

I need to create a query to return the total time spent running, so I could do this by selecting the maximum time for each routeID... which sounds simple, until you actually try and write the query!

SELECT time, sessionID FROM waypoint ORDER BY time DESC;

...will return all waypoints, ordered by time; so how can I modify this to only return only one row (containing the longest time for that route) for each route?

Thanks
Alex

 

[mark66]

I haven't worked with MYSQL, but it looks pretty standard to me so this is how I would try:

SELECT RouteID, SessionID, MAX(Time)
From Waypoint
Group By RouteID, SessionID

This will return the highest value of time stored for each Route for each Session.

Use MAX if your Time field is being added to each previous time. If the time is only for each waypoint and not a running total then use SUM(Time) instead.

 

[bigred]

Thanks that worked great