From: Bob <BHockney@ix.netcom.com>
To: PFC <lists@boutiquenumerique.com>
To: pgsql-sql@postgresql.org
Subject: Re: "How do I ..." SQL question
Date: Tue, 18 Jan 2005 19:11:30 -0800
Message-ID: <41ED5F62.1270.30449FEC@localhost> (raw)
In-Reply-To: <opskruwbvwth1vuj@musicbox>
References: <31958303.1106004700938.JavaMail.root@dewey.psp.pas.earthlink.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: BHockney@ix.netcom.com, lists@boutiquenumerique.com
Subject: Re: "How do I ..." SQL question
In-Reply-To: <41ED5F62.1270.30449FEC@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox