Pulling additional columns with aggregate

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Pulling additional columns with aggregate
2+ messages / 2 participants
[nested] [flat]

* Pulling additional columns with aggregate
@ 2009-10-08 13:14 sub3 <steve@subwest.com>
  2009-10-08 14:44 ` Re: Pulling additional columns with aggregate Lennin Caro <lennin.caro@yahoo.com>
  0 siblings, 1 reply; 2+ messages in thread

From: sub3 @ 2009-10-08 13:14 UTC (permalink / raw)
  To: pgsql-sql

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.

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Pulling additional columns with aggregate
  2009-10-08 13:14 Pulling additional columns with aggregate sub3 <steve@subwest.com>
@ 2009-10-08 14:44 ` Lennin Caro <lennin.caro@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Lennin Caro @ 2009-10-08 14:44 UTC (permalink / raw)
  To: pgsql-sql@postgresql.org, sub3 <steve@subwest.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





      

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2009-10-08 14:44 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-08 13:14 Pulling additional columns with aggregate sub3 <steve@subwest.com>
2009-10-08 14:44 ` Lennin Caro <lennin.caro@yahoo.com>

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