[thelist] MySQL "group by" problem

[Paul Cowan]

On 26/03/2004 5:25 PM +0100 Edwin Martin wrote:
> But... It doesn't work. For relatienr 102 and 103 it returns the medium
> 0, although there are mediums 3 and 4.

This is because you have:
	where medium in ( 0, 5 )

GROUP BY clauses are always applied AFTER the WHERE clause.

In a way, it kind of goes:
	- Apply WHERE clause
	- Apply aggregates (SUM, etc), and GROUP BYs
	- Apply HAVINGs

So you're filtering out all the 3 and 4 rows BEFORE you do the MAX and the 
GROUP BYs.

These rows:
| Bakker   |       102 |      3 |
| De Groot |       103 |      4 |
are effectively removed, and then your query applied to that.

Cheers,

Paul