Pulling additional columns with aggregate

Hi,

I have 2 tables.  I want to be able to calculate the closest value in one
(tempvalues), to the closest value in the other (points).  This closest
point, I want to save into the table with its difference.

So if I have:

create table points ( id integer, center double precision
);
insert into points values (1, 1),(2,4),(3,7),(4,12);


CREATE TABLE tempvalues ( id serial NOT NULL, "value" double precision, closest_point_id integer, distance_to_point
doubleprecision, CONSTRAINT tempvalues_pkey PRIMARY KEY (id), CONSTRAINT tempvalues_closest_point_id_fkey FOREIGN KEY
(closest_point_id)    REFERENCES points (id) MATCH SIMPLE     ON UPDATE NO ACTION ON DELETE NO ACTION
 
);
insert into tempvalues (value) values
(1.1),(2.2),(3.3),(4.4),(5.5),(6.6),(7.7),(8.8),(9.9),
(10.1),(11.1),(12.2),(13.3),(14.4),(15.5),(16.6),(17.7),(18.8),(19.9),(20.0);


I would like to see each row in tempvalues populated with the closest point
from points and its difference.

I know I can find the cartesian product of the 2 tables, and get the
distance between all values. select tempvalues.id as tid, points.id as pid,
min(abs(points.center-tempvalues.value)) from points, tempvalues group by tempvalues.id,points.id  order by tid,pid

But I can't figure out how to return the result w/only 1 row per
tempvalue.id (the minimum) and still get the id column from each table. Any
aggregate with force those columns out.


I would love to do something like: update tempvalues set closest_point_id,distance_to_point from (above
query)
but haven't been able to figure this out.  Any suggestions?

Thanks.
-- 
View this message in context: http://www.nabble.com/Pulling-additional-columns-with-aggregate-tp25802979p25802979.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.