Message-ID: 
 <4495118.1106674353626.JavaMail.root@misspiggy.psp.pas.earthlink.net>
Date: Tue, 25 Jan 2005 09:32:33 -0800 (GMT-08:00)
From: zeus@ix.netcom.com
Reply-To: zeus@ix.netcom.com
To: pgsql-sql@postgresql.org
Subject: Re: "How do I ..." SQL question
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi there:

Thank you for the response, which gave me what I wanted.
Here is a follow-up question..

First a recap:

Table a contains the names of individuals, the places
they have visited and the year in which they were visited.

Let's see who has visited where and when:

SELECT * FROM a;

 name   place   year
------ ------- ------
 kim    north   2004
 kim    south   2003
 kim    south   2003
 bob    west    2004
 bob    west    2004
 bob    west    2003
 joe    south   2004
 joe    south   2005
 sue    west    2004
 bob    east    2003
 joe    east    2004
 joe    east    2004
 sue    south   2004
 bob    north   2004
 bob    north   2005

Summarize data by number of places visited by year:

SELECT count(*) AS count, name, year FROM a
  GROUP BY name, year
  ORDER BY count DESC, name ASC;

 count   name   year
------- ------ ------
   3     bob    2004
   3     joe    2004
   2     bob    2003
   2     kim    2003
   2     sue    2004
   1     bob    2005
   1     joe    2005
   1     kim    2004

Return only four rows beginning at second row into temporary table:

CREATE TEMPORARY TABLE output AS 
  SELECT count(*) AS count, name, year FROM a
    GROUP BY name, year
    ORDER BY count DESC, name ASC
    LIMIT 4 OFFSET 1;

SELECT * FROM output;

 count   name   year
------- ------ ------
   3     joe    2004 s,e,e
   2     bob    2003 w,e
   2     kim    2003 s,s
   2     sue    2004 s,w

Select only places visited included in LIMITed query:

SELECT DISTINCT a.place FROM a, output
  WHERE a.name=output.name AND a.year=output.year;

 place    
-------
 south
 west
 east


Here is the new question.  I want to use the above result to update
another table which contains unique places visited and also has a field
to indicate recently visited places already present.

SELECT * FROM places;

 refresh   place
--------- -------
    0      south
    0      west
    0      southwest

(The following two queries do not work right and are what I need help with)

Add new places:

INSERT INTO places (refresh, place)
  SELECT DISTINCT 1, a.place FROM a, output
    LEFT JOIN places ON places.place=a.place
    WHERE a.name=output.name AND a.year=output.year
      AND places.place IS NULL;

Update refresh flag for existing places.  Note: the refresh field
can have one of several values and I only want to change it when
it has a particular value.

UPDATE places SET refresh=1
  FROM output, a
  WHERE places.refresh=0
    AND places.place=a.place
    AND a.name=output.name AND a.year=output.year;

(The last query never updates the places table
 and I'm not sure how to do this)

I want this result:

SELECT * FROM places;

 refresh   place
--------- -------
    1      south
    1      west
    0      southwest
    1      east

Any help appreciated.

-Bob