CODEWITHSUNDEEP
postgresqlsql-order-byaggregate-functionspostgis
Ilia Choly
Ilia Choly

Reputation: 18557

Order grouped rows before aggregate function

I have a postgis table with point geometries.

points table:

id | uid | date | geom

Points with the same uid are the same target. I'm trying to GROUP BY uid with the ST_MakeLine to create a complete target LineString.

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 doing this by adding an ORDER BY date before 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 order the grouped rows before they are fed into the aggregate function?

Upvotes: 13

Views: 3120

Answers (2)

Ilia Choly
Ilia Choly

Reputation: 18557

The ORDER BY clause can be placed at the end of the aggregate arguments.

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

http://www.postgresql.org/docs/9.1/static/sql-expressions.html#SYNTAX-AGGREGATES

Upvotes: 16

gts
gts

Reputation: 627

Maybe have the data you want ordered in a temp table, and then group externally by uid?

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

Upvotes: 1

Related Questions