Arrange rows before aggregate function

Question

I have a postgis table with point geometries.

id | uid | date | geom

Points with the same uidare the same goal. I am trying GROUP BY uidwith ST_MakeLine to create a full LineString target string.

SELECT uid, ST_MakeLine(geom)
FROM points
GROUP BY uid

This works, but I want to make sure the points are in the correct order. I tried to do this by adding ORDER BY datebefore grouping.

SELECT uid, ST_MakeLine(geom)
FROM points
ORDER BY date <-- does not work
GROUP BY uid

ERROR: syntax error at or near "GROUP"

Is there a way to arrange grouped rows before they are loaded into an aggregate function?

+3

Ilia Choly Feb 18 '14 at 16:19

2 answers

, , temp, uid?

 SELECT uid, ST_MakeLine(geom)
 FROM
 (
    SELECT uid, geom
    FROM points
    ORDER BY date
 ) AS temp
 GROUP BY uid

+1

gts 18 . '14 16:23

Ilia Choly · Accepted Answer · 2014-02-18T16:43:22+0000

A sentence ORDER BYcan be placed at the end of aggregate arguments.

SELECT uid, ST_MakeLine(geom ORDER BY date)
FROM points
GROUP BY uid