Re: Need help with a query

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