[SQL] "How do I ..." SQL question

[zeus]

Hi there:

I have a "How do I..." SQL question regarding selecting
distinct values from a field not included in an aggregated
query when LIMIT is in effect, illustrated by the
following example:

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
-- --- --
 kimnorth   2004
 kimsouth   2003
 kimsouth   2003
 bobwest2004
 bobwest2004
 bobwest2003
 joesouth   2004
 joesouth   2005
 suewest2004
 bobeast2003
 joeeast2004
 joeeast2004
 suesouth   2004
 bobnorth   2004
 bobnorth   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 bob2004
   3 joe2004
   2 bob2003
   2 kim2003
   2 sue2004
   1 bob2005
   1 kim2004
   1 joe2005

Return only four rows beginning at second row:

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

 count   name   year
--- -- --
   3 joe2004 s,e,e
   2 bob2003 w,e
   2 kim2003 s,s
   2 sue2004 s,w

Select only places visited included in LIMITed query:

SELECT DISTINCT place FROM a ;

 place
---
 south
 west
 east

Note that the place north does not appear in the last result
because north was only visited by bob in 2005 and kim in 2004,
records which are not included in the limited result.

Any help appreciated.

-Bob

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] "How do I ..." SQL question

[zeus]

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
-- --- --
 kimnorth   2004
 kimsouth   2003
 kimsouth   2003
 bobwest2004
 bobwest2004
 bobwest2003
 joesouth   2004
 joesouth   2005
 suewest2004
 bobeast2003
 joeeast2004
 joeeast2004
 suesouth   2004
 bobnorth   2004
 bobnorth   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 bob2004
   3 joe2004
   2 bob2003
   2 kim2003
   2 sue2004
   1 bob2005
   1 joe2005
   1 kim2004

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 joe2004 s,e,e
   2 bob2003 w,e
   2 kim2003 s,s
   2 sue2004 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

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq