Thanks a lot Birgit, works just fine. Regards
Julio Almeida On Thu, Mar 17, 2011 at 8:50 AM, Birgit Laggner <[email protected]>wrote: > Hi Júlio, > > you could use DISTINCT ON: > > select distinct on (point_id) point_id, line_id, distance from > worktemp.distances2streets order by point_id, distance; > > This selects for every point_id the first row of every point_id group. > Since you have ordered by distance too, the shortest distance is always on > top of the list and will therefore be selected. > > Regards, > > Birgit. > > > On 16.03.2011 12:54, Júlio Almeida wrote: > > Hello, > > I have a table with point_id, line_id, and distance (from point to line). > I need to create a new table with the record for the shortest distance for > each point. > I have tried: > > select point_id, > line_id, > min(distance) > from worktemp.distances2streets group by point_id; > > but i get the message: > > ERROR: column "distances2streets.line_id" must appear in the GROUP BY > clause or be used in an aggregate function > LINE 2: line_id, > ^ > > ********** Erro ********** > > ERROR: column "distances2streets.line_id" must appear in the GROUP BY > clause > or be used in an aggregate function > Estado de SQL:42803 > Carácter:18 > > > Any suggestion? > > Thanks > > > _______________________________________________ > postgis-users mailing > [email protected]http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
