From: Simon Kinsella <simon@bluefiresystems.co.uk>
To: pgsql-sql@postgresql.org
Subject: GROUPing only those rows that do not contain a NULL field?
Date: Fri, 11 Feb 2005 20:44:01 -0000
Message-ID: <20050211204404.21ECE8BA15C@svr1.postgresql.org> (raw)
Hi
I wonder if anyone can help me with a SELECT / GROUP BY problem I'm having.
I'm trying to refine a query so that my GROUP BY clause only aggregates rows
that have a non-NULL value in one of the fields, leaving other rows
'ungrouped'.
An example table, resulting from a LEFT JOIN and subselect on three tables,
might be (before grouping) :
SELECT user_group_id, user_id, topic_id FROM user_groups NATURAL JOIN users
LEFT JOIN
(SELECT user_id, topic_id FROM topic_participants WHERE topic_id = 567) AS a
USING (user_id)
user_group_id | user_id | topic_id
-----------------------------------------
1 101 NULL
1 102 567
1 103 567
2 101 NULL
2 106 567
3 101 NULL
3 104 567
3 102 567
4 103 567
4 104 567
5 105 NULL
6 103 567
6 104 567
(topic id is either a single value, or NULL if the user is not part of the
topic)
For UI reasons I would like this result collapsed thus:
user_group_id | user_id | topic_id
-----------------------------------------
1 101 NULL
1 102 567
1 103 567
2 101 NULL
2 106 567
3 101 NULL
3 104 567
5 105 NULL
Note that for all rows where topic_id IS NOT NULL, only one row per user_id
is returned (user_group_id is not relevant for these rows).
Can this aggregation be achieved with a DISTINCT ON / GROUP BY clause? (Or
anything else). I haven't managed to find anything that leaves the
NULL-field rows unscathed so far.
Thanks in advance for any help!
Simon K
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: simon@bluefiresystems.co.uk
Subject: Re: GROUPing only those rows that do not contain a NULL field?
In-Reply-To: <20050211204404.21ECE8BA15C@svr1.postgresql.org>
* 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