From: "Bob" <BHockney@ix.netcom.com>
To: PFC <lists@boutiquenumerique.com>, pgsql-sql@postgresql.org
Date: Tue, 18 Jan 2005 19:11:30 -0800
MIME-Version: 1.0
Subject: Re: "How do I ..." SQL question
Message-ID: <41ED5F62.1270.30449FEC@localhost>
In-reply-to: <opskruwbvwth1vuj@musicbox>
References: <31958303.1106004700938.JavaMail.root@dewey.psp.pas.earthlink.net>
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body

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