[postgis-users] dissolve attributes in a table (no geometry)

[Birgit Laggner]

  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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110317/e2225c57/attachment.html>