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

[Júlio Almeida]

Thanks a lot Birgit, works just fine.

Regards

Julio Almeida

On Thu, Mar 17, 2011 at 8:50 AM, Birgit Laggner
<birgit.laggner at vti.bund.de>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 listpostgis-users at postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> 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/a30200f8/attachment.html>