MySQL sort data before using GROUP_CONCAT to concat it together? I have a system that tracks several thousand objects in realtime and stores their locations in a database table.  Rather than store their location every few seconds I simply store their location, direction, speed and time whenever it changes.  This keeps the table relatively small for the number of objects that I'm tracking.

Each row contains data on a single object at a moment in time and whilst these should go into the database in chronological I want to ensure that I pull the data out in chronological order before concat'ing them together so that I can plot out the data.

My table structure is:

CREATE TABLE tracks (
    id int(11) auto_increment,
    objectId int(11) default 0,
    posTime bigint(15) default 0,
    X float(6,4) default 0.0000,
    Y float(6,4) default 0.0000
    Z int(11) default 0,
    hdg float(4,1) default 0.0,
    spd float(5,1) default 0,
    PRIMARY KEY(id));


By using this query:

SELECT   objectId, 
         group_concat(X, ',', Y, ',', hdg, ',', spd separator ',') as trail 
FROM     tracks 
WHERE    objectId &gt; 0 
group by objectId;


I get the data that I want in the format that I want e.g.:

14979491 | -26.3286,-48.4679,206.0,362.0,-26.3684,-48.4892,205.0,357.0


i.e. ObjectId | X1, Y1, heading1, speed1, X2, Y2, heading2, speed2, .... etc

However I'm not 100% confident that the position reports are in the correct time order.

How can I sort the position reports for each id by increasing posTime before concat'ing them together ?  In theory it's just a case of adding ORDER BY posTime ASC but I'm not sure how or where to add this to my query as all previous attempts have failed ...

EDITED TO CLARIFY: I know that group_concat supports an ORDER BY clause but I don't want the column that I sort by (posTime) to be returned in the results.
You need to get confidence in GROUP_CONCAT with ORDER BY embedded.
Only way to do that is the following
SUGGESTION 01 : Proper Indexing
ALTER TABLE tracks ADD INDEX objectId_posTime_ndx (objectId,posTime);

This will help the query optimizer retrieve your data ordered properly.
SUGGESTION 02 : Display posTime as a Debug Step
SELECT   objectId, 
         group_concat(posTime ORDER BY posTime) as trail 
FROM     tracks 
WHERE    objectId &gt; 0 
GROUP BY objectId;

If the posTime is ordered as desired, then you should be fine with
SELECT   objectId, 
         group_concat(CONCAT(X, ',', Y, ',', hdg, ',', spd) ORDER BY posTime) as trail 
FROM     tracks 
WHERE    objectId &gt; 0 
group by objectId;