MySQL Forums
Forum List  »  Newbie

Re: Grouping the result of a union
Posted by: Roland Bouman
Date: July 26, 2005 02:21AM

Your problem is that the GROUP BY is applied before the union.
I guess the ORDER BY is applied after the UNIONs because it does not make sense to order a temporary result (e.g. the query before the union takes place) whereas it could make sense to apply a GROUP BY.

if it was mysql 4.1, you could do:

SELECT ...
FROM (
SELECT ..
..
UNION ALL
SELECT ..
..
) as union_query
GROUP BY ..
...

which relies on a subquery (I must say im surprised that mysql 4.0 accepts the queries between parentheses at all as you describe).

As for the solutution, couldnt you do something like this?

select w.word_id
from word_table w
left join search_match_summary s
on w.word_id = s.word_id
left join search_match_comment c
on w.word_id = c.word_id
left join search_match_keyword k
on w.word_id = k.word_id
group by w.word_id
having count(distinct key_of_s)
+ count(distinct key_of_c)
+ count(distinct key_of_k)
= X

of course, this relies on your match tables having a primary key that you can count distinct.

Options: ReplyQuote


Subject
Written By
Posted
Re: Grouping the result of a union
July 26, 2005 02:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.