Re: Pulling additional columns with aggregate - Mailing list pgsql-sql

<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">--- On <b>Thu, 10/8/09,
sub3<i><steve@subwest.com></i></b> wrote:<br /><blockquote style="border-left: 2px solid rgb(16, 16, 255);
margin-left:5px; padding-left: 5px;"><br />From: sub3 <steve@subwest.com><br />Subject: [SQL] Pulling additional
columnswith aggregate<br />To: pgsql-sql@postgresql.org<br />Date: Thursday, October 8, 2009, 1:14 PM<br /><br /><div
class="plainMail"><br/>Hi,<br /><br />I have 2 tables.  I want to be able to calculate the closest value in one<br
/>(tempvalues),to the closest value in the other (points).  This closest<br />point, I want to save into the table with
itsdifference.<br /><br />So if I have:<br /><br />create table points (<br />  id integer,<br />  center double
precision<br/>);<br />insert into points values (1, 1),(2,4),(3,7),(4,12);<br /><br /><br />CREATE TABLE tempvalues
(<br/>  id serial NOT NULL,<br />  "value" double precision,<br />  closest_point_id integer,<br />  distance_to_point
doubleprecision,<br />  CONSTRAINT tempvalues_pkey PRIMARY KEY (id),<br />  CONSTRAINT tempvalues_closest_point_id_fkey
FOREIGNKEY (closest_point_id)<br />      REFERENCES points (id) MATCH SIMPLE<br />      ON UPDATE NO ACTION ON DELETE
NOACTION<br />);<br />insert into tempvalues (value) values<br
/>(1.1),(2.2),(3.3),(4.4),(5.5),(6.6),(7.7),(8.8),(9.9),<br
/>(10.1),(11.1),(12.2),(13.3),(14.4),(15.5),(16.6),(17.7),(18.8),(19.9),(20.0);<br/><br /><br />I would like to see
eachrow in tempvalues populated with the closest point<br />from points and its difference.<br /><br />I know I can
findthe cartesian product of the 2 tables, and get the<br />distance between all values.<br />  select tempvalues.id as
tid,points.id as pid,<br />min(abs(points.center-tempvalues.value))<br />  from points, tempvalues group by
tempvalues.id,points.id order by tid,pid<br /><br />But I can't figure out how to return the result w/only 1 row per<br
/>tempvalue.id(the minimum) and still get the id column from each table. Any<br />aggregate with force those columns
out.<br/><br /><br />I would love to do something like:<br />  update tempvalues set closest_point_id,distance_to_point
from(above<br />query)<br />but haven't been able to figure this out.  Any suggestions?<br /><br />Thanks.<br />-- <br
/>Viewthis message in context: <a
href="http://www.nabble.com/Pulling-additional-columns-with-aggregate-tp25802979p25802979.html"
target="_blank">http://www.nabble.com/Pulling-additional-columns-with-aggregate-tp25802979p25802979.html</a><br/>Sent
fromthe PostgreSQL - sql mailing list archive at Nabble.com.<br /><br /><br />-- <br />Sent via pgsql-sql mailing list
(<ahref="/mc/compose?to=pgsql-sql@postgresql.org"
ymailto="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/>To make changes to your subscription:<br
/><ahref="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/><br /></div></blockquote>this query return de min
valueand both id<br /><br /><br />Select q11.tid, min_value2<br />from (<br /><br />    select tid, min(min_value) as
min_value2<br/>    from (<br />        select tempvalues.id as tid, points.id as pid,<br />       
min(abs(points.center-tempvalues.value))as min_value<br />        from points, tempvalues group by
tempvalues.id,points.id order by tid,pid<br />    ) as q1<br />    group by tid<br />    ) as q11, <br />    (<br />   
selecttempvalues.id as tid, points.id as pid,<br />    min(abs(points.center-tempvalues.value)) as min_value<br />   
frompoints, tempvalues group by tempvalues.id,points.id  order by tid,pid<br />    ) as q2<br />  where q11.tid =
q2.tidand q11.min_value2 = q2.min_value<br /><br /><div class="plainMail"><br /></div></td></tr></table><br />