To: zeus@ix.netcom.com, pgsql-sql@postgresql.org
Subject: Re: "How do I ..." SQL question
References: <31958303.1106004700938.JavaMail.root@dewey.psp.pas.earthlink.net>
Message-ID: <opskruwbvwth1vuj@musicbox>
Date: Tue, 18 Jan 2005 01:13:13 +0100
From: PFC <lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <31958303.1106004700938.JavaMail.root@dewey.psp.pas.earthlink.net>
User-Agent: Opera M2/7.54 (Linux, build 751)

  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
>