Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 19F66632C8F for ; Thu, 8 Oct 2009 11:45:04 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 31269-06 for ; Thu, 8 Oct 2009 14:44:43 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from web59506.mail.ac4.yahoo.com (web59506.mail.ac4.yahoo.com [76.13.12.165]) by mail.postgresql.org (Postfix) with SMTP id 85924632260 for ; Thu, 8 Oct 2009 11:44:52 -0300 (ADT) Received: (qmail 34123 invoked by uid 60001); 8 Oct 2009 14:44:49 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1255013089; bh=oO3ppVb7upa4xlfSBWo8zCtHGJ3dwURzKY5Q61mjGzY=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=4SXTziphhIXynpHmO/yECTXu+f/Jkg+sVHOmva5lHMITopqQtRHiZpfxBZUr3Zwl9oyG/30B0jnaEgAr8S0pzmAs0pnCc80Gm7jfE0Z4j8xlacG1AX/AwthBqNk7GPi1Cf9qfCbO5HsnpgBZUhTQuM90v46zplOxs/QGuJhPTO0= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=4NI1KjESvTCKv/7MyhQwtEIkxn8SbQ7kn/0AHohwNrS5T1BvisnjsI4gsDj1SnNo2Il3DQm+kjluWCVu4XlMCBbXAczEjaj0/QKu7nhGNbpNN7fRxXuS2AdeH6b6De4rUdTsz2zqZiVWY9bDopd4TUhp0skLNbjKwEaU07OeM+A=; Message-ID: <18332.26910.qm@web59506.mail.ac4.yahoo.com> X-YMail-OSG: o54NQ10VM1lyWt9uL9H3eokwrYMY9eDOjxxHLdlfyNPo807GEeOLlj6_rB7lsOaZWjoP4M.5GS5od80sdDv45vzG2dXIknfIezUESaJ9Up5qX.xYYq2tiUkeJRAwxD7vqjNOrM9IkxcFGvEWT745r4iB7CESd6LCdTdgByqoFuF99uHxDat7vc1UHs2ALA18UPNm78bW3XhW614j2lXtkCBvEqCjmii0sDsJXIRBb5s0mB0mwANL3NKiezZyd4prkjDgk7kUVZ8JrVndtlcdzx8eMIb1doAaw_YQLrrM8aM6voARfOYgMuXNrSxJ_uyJ0qpx8rNtMNLtFcsbYLXoTAnAPjmVbGkNjEnd0J4fKbigww-- Received: from [161.196.9.37] by web59506.mail.ac4.yahoo.com via HTTP; Thu, 08 Oct 2009 07:44:48 PDT X-Mailer: YahooMailClassic/7.0.14 YahooMailWebService/0.7.347.3 Date: Thu, 8 Oct 2009 07:44:48 -0700 (PDT) From: Lennin Caro Subject: Re: Pulling additional columns with aggregate To: pgsql-sql@postgresql.org, sub3 In-Reply-To: <25802979.post@talk.nabble.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-959511593-1255013088=:26910" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.639 tagged_above=-10 required=5 tests=AWL=-0.455, BAYES_40=-0.185, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200910/29 X-Sequence-Number: 33228 --0-959511593-1255013088=:26910 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable --- On Thu, 10/8/09, sub3 wrote: From: sub3 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.=A0 I want to be able to calculate the closest value in one (tempvalues), to the closest value in the other (points).=A0 This closest point, I want to save into the table with its difference. So if I have: create table points ( =A0 id integer, =A0 center double precision ); insert into points values (1, 1),(2,4),(3,7),(4,12); CREATE TABLE tempvalues ( =A0 id serial NOT NULL, =A0 "value" double precision, =A0 closest_point_id integer, =A0 distance_to_point double precision, =A0 CONSTRAINT tempvalues_pkey PRIMARY KEY (id), =A0 CONSTRAINT tempvalues_closest_point_id_fkey FOREIGN KEY (closest_point_= id) =A0 =A0 =A0 REFERENCES points (id) MATCH SIMPLE =A0 =A0 =A0 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. =A0 select tempvalues.id as tid, points.id as pid, min(abs(points.center-tempvalues.value)) =A0 from points, tempvalues group by tempvalues.id,points.id=A0 order by ti= d,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: =A0 update tempvalues set closest_point_id,distance_to_point from (above query) but haven't been able to figure this out.=A0 Any suggestions? Thanks. --=20 View this message in context: http://www.nabble.com/Pulling-additional-colu= mns-with-aggregate-tp25802979p25802979.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. --=20 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 ( =A0=A0=A0 select tid, min(min_value) as min_value2 =A0=A0=A0 from ( =A0=A0=A0 =A0=A0=A0 select tempvalues.id as tid, points.id as pid, =A0=A0=A0 =A0=A0=A0 min(abs(points.center-tempvalues.value)) as min_value =A0=A0=A0 =A0=A0=A0 from points, tempvalues group by tempvalues.id,points.i= d=A0 order by tid,pid =A0=A0=A0 ) as q1 =A0=A0=A0 group by tid =A0=A0=A0 ) as q11,=20 =A0=A0=A0 ( =A0=A0=A0 select tempvalues.id as tid, points.id as pid, =A0=A0=A0 min(abs(points.center-tempvalues.value)) as min_value =A0=A0=A0 from points, tempvalues group by tempvalues.id,points.id=A0 order= by tid,pid =A0=A0=A0 ) as q2 =A0 where q11.tid =3D q2.tid and q11.min_value2 =3D q2.min_value =0A=0A=0A --0-959511593-1255013088=:26910 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
--- On Thu, 10/8/09, sub3 <steve@sub= west.com> wrote:

From: sub3 <= steve@subwest.com>
Subject: [SQL] Pulling additional columns with agg= regate
To: pgsql-sql@postgresql.org
Date: Thursday, October 8, 2009, = 1:14 PM


Hi,

I have 2 tables.&nbs= p; 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:
<= br>create table points (
  id integer,
  center double prec= ision
);
insert into points values (1, 1),(2,4),(3,7),(4,12);

=
CREATE TABLE tempvalues (
  id serial NOT NULL,
  "valu= e" double precision,
  closest_point_id integer,
  distance_t= o_point double precision,
  CONSTRAINT tempvalues_pkey PRIMARY KEY = (id),
  CONSTRAINT tempvalues_closest_point_id_fkey FOREIGN KEY (cl= osest_point_id)
      REFERENCES points (id) MATCH SIMPLE=
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
i= nsert 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 tempva= lues populated with the closest point
from points and its difference.
I know I can find the cartesian product of the 2 tables, and get thedistance between all values.
  select tempvalues.id as tid, point= s.id as pid,
min(abs(points.center-tempvalues.value))
  from poi= nts, 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 ro= w per
tempvalue.id (the minimum) and still get the id column from each t= able. Any
aggregate with force those columns out.


I would lov= e to do something like:
  update tempvalues set closest_point_id,di= stance_to_point from (above
query)
but haven't been able to figure th= is out.  Any suggestions?

Thanks.
--
View this message i= n context: http://www.nabble.co= m/Pulling-additional-columns-with-aggregate-tp25802979p25802979.htmlSent from the PostgreSQL - sql mailing list archive at Nabble.com.

=
--
Sent via pgsql-sql mailing list (pgsql-sq= l@postgresql.org)
To make changes to your subscription:
ht= tp://www.postgresql.org/mailpref/pgsql-sql

th= is query return de min value and both id


Select q11.tid, min_val= ue2
from (

    select tid, min(min_value) as min_v= alue2
    from (
       = select tempvalues.id as tid, points.id as pid,
     = ;   min(abs(points.center-tempvalues.value)) as min_value
&nbs= p;       from points, tempvalues group by tempvalu= es.id,points.id  order by tid,pid
    ) as q1
&nb= sp;   group by tid
    ) as q11,
 &nbs= p;  (
    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 =3D q2.tid and q11.min_value2 =3D q2.min_value


=0A=0A --0-959511593-1255013088=:26910--