From: Lennin Caro <lennin.caro@yahoo.com>
To: pgsql-sql@postgresql.org, sub3 <steve@subwest.com>
Subject: Re: Pulling additional columns with aggregate
Date: Thu, 8 Oct 2009 07:44:48 -0700 (PDT)
Message-ID: <18332.26910.qm@web59506.mail.ac4.yahoo.com> (raw)
In-Reply-To: <25802979.post@talk.nabble.com>
--- On Thu, 10/8/09, sub3 <steve@subwest.com> wrote:
From: sub3 <steve@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lennin.caro@yahoo.com, steve@subwest.com
Subject: Re: Pulling additional columns with aggregate
In-Reply-To: <18332.26910.qm@web59506.mail.ac4.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox