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 list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
