From: zeus@ix.netcom.com
To: pgsql-sql@postgresql.org
Subject: "How do I ..." SQL question
Date: Mon, 17 Jan 2005 15:31:40 -0800 (GMT-08:00)
Message-ID: <31958303.1106004700938.JavaMail.root@dewey.psp.pas.earthlink.net> (raw)
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 2004
kim south 2003
kim south 2003
bob west 2004
bob west 2004
bob west 2003
joe south 2004
joe south 2005
sue west 2004
bob east 2003
joe east 2004
joe east 2004
sue south 2004
bob north 2004
bob 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
-------
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
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: zeus@ix.netcom.com
Subject: Re: "How do I ..." SQL question
In-Reply-To: <31958303.1106004700938.JavaMail.root@dewey.psp.pas.earthlink.net>
* 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