--- On Thu, 10/8/09, sub3 <st...@subwest.com> wrote: From: sub3 <st...@subwest.com> Subject: [SQL] Pulling additional columns with aggregate To: pgsql-sql@postgresql.org Date: Thursday, October 8, 2009, 1:14 PM
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 double precision, 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql this query return de min value and both id Select q11.tid, min_value2 from ( select tid, min(min_value) as min_value2 from ( select tempvalues.id as tid, points.id as pid, min(abs(points.center-tempvalues.value)) as min_value from points, tempvalues group by tempvalues.id,points.id order by tid,pid ) as q1 group by tid ) as q11, ( select tempvalues.id as tid, points.id as pid, min(abs(points.center-tempvalues.value)) as min_value from points, tempvalues group by tempvalues.id,points.id order by tid,pid ) as q2 where q11.tid = q2.tid and q11.min_value2 = q2.min_value
