Skip site navigation (1) Skip section navigation (2)

Re: Need help with a query

From: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "A B" <gentosaker(at)gmail(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: Need help with a query
Date: 2010-10-26 09:39:19
Message-ID: 2362F87A20BF4CFBBE2041E461DC28A4@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: pgsql-novice
Howdy,
AB

Please check if this (untested) query produces the result you want.

Best,
Oliver

UPDATE foo
SET last_seen = subquery.maximo
FROM 
(
SELECT x,y,MAX(seen) as maximo
FROM bar
GROUP BY x,y
) as subquery
WHERE x = subquery.x
AND y = subquery.y

----- Original Message ----- 
From: "A B" <gentosaker(at)gmail(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Sent: Tuesday, October 26, 2010 10:16 AM
Subject: [NOVICE] Need help with a query


> Hello.
> 
> I'm having a problem with  a probably very simple query.
> I need to update a table
> foo (x int, y int, last_seen timestamp, unique(x,y));
> 
> where the values should be taken from a larger table
> bar( x int, y int, seen timestamp);
> where each x,y combination occurs several times, and the value in
> foo.last_seen should be the value max(seen) for each pair of x,y.
> Notice! All combinations of x,y in bar are also in foo, but not the
> other way around.
> 
> So how do I write a query for this?
> Thanks in advance.
> 
> -- 
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

In response to

pgsql-novice by date

Next:From: scorpdaddyDate: 2010-10-26 10:24:51
Subject: Re: [NOVICE] Need help with a query
Previous:From: A BDate: 2010-10-26 09:16:58
Subject: Need help with a query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group