Thread: "How do I ..." SQL question

"How do I ..." SQL question

From
zeus@ix.netcom.com
Date:
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
------ ------- ------kim    north   2004kim    south   2003kim    south   2003bob    west    2004bob    west    2004bob
  west    2003joe    south   2004joe    south   2005sue    west    2004bob    east    2003joe    east    2004joe
east   2004sue    south   2004bob    north   2004bob    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     kim    2004  1     joe    2005
 

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     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 place FROM a ????;
place    
-------southwesteast

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


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

From
PFC
Date:
  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     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:

Is this :
SELECT DISTINCT place FROM a,(
SELECT count(*) AS count, name, year FROM a   GROUP BY name, year   ORDER BY count DESC, name ASC   LIMIT 4 OFFSET 1
) as foo WHERE name=foo.name AND year=foo.year
Problem with this approach is that you'll have to run the query twice,  
one to get the hitlist by user, one for the places...

>
> 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: "How do I ..." SQL question

From
zeus@ix.netcom.com
Date:
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   2004kim    south   2003kim    south   2003bob    west    2004bob    west    2004bob
  west    2003joe    south   2004joe    south   2005sue    west    2004bob    east    2003joe    east    2004joe
east   2004sue    south   2004bob    north   2004bob    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    
-------southwesteast


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
ANDa.year=output.year;
 

(The last query never updates the places tableand 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


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

From
"Bob"
Date:
PFC wrote:

>   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     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:
> 
> 
>     Is this :
> 
>     SELECT DISTINCT place FROM a,(
> SELECT count(*) AS count, name, year FROM a
>     GROUP BY name, year
>     ORDER BY count DESC, name ASC
>     LIMIT 4 OFFSET 1
> ) as foo WHERE name=foo.name AND year=foo.year
> 
>     Problem with this approach is that you'll have to run the query twice,  
> one to get the hitlist by user, one for the places...
> 
> >
> > SELECT DISTINCT place FROM a ????;
> >
> >  place
> > -------
> >  south
> >  west
> >  east

Thanks, this worked.  As it happens, I am already creating a temporary table
for the hitlist for other uses so that isn't a problem.

-Bob