[SQL] 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 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Data into an array
Hi, I am trying to compress some data down into a single row (making it an array). I believe I am close, but I still keep getting an error: "ERROR: query has no destination for result data" Does anyone have an idea what is going wrong? Thanks. CREATE or REPLACE FUNCTION getVSS(f_pvid integer, f_time timestamp, OUT o_port integer[], OUT o_conf double precision[], OUT d_port integer[], OUT d_conf double precision[]) RETURNS setof record as $$ DECLARE vssview RECORD; BEGIN o_port = ARRAY[0]; o_conf = ARRAY[0]; d_port = ARRAY[0]; d_conf = ARRAY[0]; FOR vssview IN select vss_orig.portid, vss_orig.confidence from vss, vss_orig where vss.vssmsg = vss_orig.vssmsg AND pvid = f_pvid and f_time between starttime and endtime LOOP select array_append(o_port, vssview.portid); select array_append(o_conf, vssview.confidence); END LOOP; FOR vssview IN select vss_dest.portid, vss_dest.confidence from vss, vss_dest where vss.vssmsg = vss_dest.vssmsg AND pvid = f_pvid and f_time between starttime and endtime LOOP select array_append(d_port, vssview.portid); select array_append(d_conf, vssview.confidence); END LOOP; END; $$ LANGUAGE plpgsql; -- View this message in context: http://www.nabble.com/Data-into-an-array-tp19167834p19167834.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Data into an array
Thanks, I got it to work. But now I have a different problem. If I execute: select * from getvss(1, now()) it works. But instead, if I execute it as: select *, getvss(d1.id, now()) from d1 I don't get separate columns for the data coming from getvss, it is all in one column, unlike the previous select. Is there anyway to force that into different columns? Thanks. CREATE or REPLACE FUNCTION getvss(f_pvid integer, f_time timestamp without time zone, OUT o_port integer[], OUT o_conf double precision[], OUT d_port integer[], OUT d_conf double precision[]) as $$ DECLARE vssview RECORD; BEGIN FOR vssview IN select vss_orig.portid, vss_orig.confidence from vss, vss_orig where vss.vssmsg = vss_orig.vssmsg AND pvid = f_pvid and f_time between starttime and endtime LOOP o_port := array_append(o_port, vssview.portid); o_conf := array_append(o_conf, vssview.confidence); END LOOP; FOR vssview IN select vss_dest.portid, vss_dest.confidence from vss, vss_dest where vss.vssmsg = vss_dest.vssmsg AND pvid = f_pvid and f_time between starttime and endtime LOOP d_port := array_append(d_port, vssview.portid); d_conf := array_append(d_conf, vssview.confidence); END LOOP; END; $$ LANGUAGE plpgsql; -- View this message in context: http://www.nabble.com/Data-into-an-array-tp19167834p19172916.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Query to match location transitions
Hi, I was hoping someone could help me build a better query. I have a table of time/locations. Occasionally, we have multiple timestamps for the same location. I would like to remove those extra timestamps and only show the transition from one location to another. So... create table time_locations ( id integer, timestamp double precision, location integer ) Data: 1,1197605841,1 2,1197608001,2 3,1197609802,2 4,1197611951,2 5,1199145360,2 6,1199145480,3 7,1199147280,3 8,1199149140,3 9,1199151300,1 10,1199152000,3 I would like to return a table like: 1197605841,1,1197608001,2 1199145360,2,1199145480,3 1199149140,3,1199151300,1 1199151300,1,1199152000,3 The only way I can think of to do this would be a procedure which would do a large loop over the table (sorted by time) returning a row when last.location <> this.location. However, when I try this on a large table, it seems like the 'select into' doesn't order & ruins the whole solution. Is there a query approach? Thanks. -- View this message in context: http://www.nabble.com/Query-to-match-location-transitions-tp20506709p20506709.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
