MySQL sort data before using GROUP_CONCAT to concat it together?

Question

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 > 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.

Answer 1

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 > 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 > 0 
group by objectId;