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.