Order rows within groups in PostgreSQL

postgresql, sql, sql-order-by

Solution

Thanks to MarcB's comment I realized the issue was with sorting within the string_agg function so the solution is:

SELECT 
  routeid, 
  'SRID=4326;LINESTRING(' || string_agg(lon || ' ' || lat, ',' ORDER BY time ASC) || ')' AS the_geom
FROM route_table 
WHERE observation_time BETWEEN '2012-09-12 10:00:00' AND '2012-09-12 10:15:00'
GROUP BY routeid HAVING COUNT(lon) > 1;

Problem

I have the following query: ``` SELECT routeid, 'SRID=4326;LINESTRING(' || string_agg(lon || ' ' || lat, ',') || ')' AS the_geom FROM route_table WHERE observation_time BETWEEN '2012-09-12 10:00:00' AND '2012-09-12 10:15:00' GROUP BY routeid HAVING COUNT(lon) > 1 ORDER BY observation_time ASC; ``` The goal of this query is to pull all lon/lat values from the route_table (which consists of a routeid, observation_time, lat, and lon columns), group them by routeid, and have them sorted within each group by the observation time. However the SQL above is not valid since observation_time appears in the ORDER BY clause by not in the GROUP BY. When I add observation_time to GROUP BY I don't get the correct result. Assuming a data set like this: ``` routeid | observation_time | lat | lon --------------------------------------------- 1 | '2012-09-12 01:00:00' | 30 | -75 1 | '2012-09-12 01:05:00' | 31 | -76 1 | '2012-09-12 01:10:00' | 31 | -76.5 2 | '2012-09-12 01:03:00' | 39 | -22 2 | '2012-09-12 01:00:00' | 40 | -22 2 | '2012-09-12 01:06:00' | 41 | -22 ``` The output should look like this: ``` routeid | the_geom -------------------------------------------------------- 1 | 'SRID=4326;LINESTRING('-75 30,-76 31,-76.5 31) 2 | 'SRID=4326;LINESTRING('-22 40,-22 39,-22 41) ``` So the question is: How do I achieve this order of the rows within groups within PostgreSQL?

Original source