pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed"How do I ..." SQL question
4+ messages / 3 participants
[nested] [flat]
* "How do I ..." SQL question
@ 2005-01-17 23:31 zeus@ix.netcom.com
0 siblings, 2 replies; 4+ messages in thread
From: zeus@ix.netcom.com @ 2005-01-17 23:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: "How do I ..." SQL question
@ 2005-01-18 00:13 PFC <lists@boutiquenumerique.com>
parent: zeus@ix.netcom.com
1 sibling, 0 replies; 4+ messages in thread
From: PFC @ 2005-01-18 00:13 UTC (permalink / raw)
To: zeus@ix.netcom.com; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: "How do I ..." SQL question
@ 2005-01-19 03:11 Bob <BHockney@ix.netcom.com>
parent: zeus@ix.netcom.com
1 sibling, 0 replies; 4+ messages in thread
From: Bob @ 2005-01-19 03:11 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: "How do I ..." SQL question
@ 2005-01-25 17:32 zeus@ix.netcom.com
0 siblings, 0 replies; 4+ messages in thread
From: zeus@ix.netcom.com @ 2005-01-25 17:32 UTC (permalink / raw)
To: pgsql-sql
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 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 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
-------
south
west
east
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 AND a.year=output.year;
(The last query never updates the places table
and 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-01-25 17:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-17 23:31 "How do I ..." SQL question zeus@ix.netcom.com
2005-01-18 00:13 ` PFC <lists@boutiquenumerique.com>
2005-01-19 03:11 ` Bob <BHockney@ix.netcom.com>
2005-01-25 17:32 Re: "How do I ..." SQL question zeus@ix.netcom.com
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