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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
