<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Júlio,<br>
<br>
you could use<tt> DISTINCT ON</tt>:<br>
<br>
select distinct on (point_id) point_id, line_id, distance from
worktemp.distances2streets order by point_id, distance;<br>
<br>
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.<br>
<br>
Regards,<br>
<br>
Birgit.<br>
<br>
On 16.03.2011 12:54, Júlio Almeida wrote:
<blockquote
cite="mid:AANLkTimhA7zUhVnuJOz9S36gNR7vw3=vDxicdNJV6s+T@mail.gmail.com"
type="cite">
<p>Hello,</p>
<p>I have a table with point_id, line_id, and distance (from point
to line).<br>
I need to create a new table with the record for the shortest
distance for<br>
each point.<br>
I have tried:</p>
<p>select point_id,<br>
line_id,<br>
min(distance)<br>
from worktemp.distances2streets group by point_id;</p>
<p>but i get the message:</p>
<p>ERROR: column "distances2streets.line_id" must appear in the
GROUP BY<br>
clause or be used in an aggregate function<br>
LINE 2: line_id,<br>
^</p>
<p>********** Erro **********</p>
<p>ERROR: column "distances2streets.line_id" must appear in the
GROUP BY clause<br>
or be used in an aggregate function<br>
Estado de SQL:42803<br>
Carácter:18</p>
<p><br>
Any suggestion?</p>
<p>Thanks</p>
<pre wrap="">
<fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>