[thelist] MySQL "group by" problem

Joshua Olson joshua at waetech.com
Sat Mar 27 20:36:43 CST 2004


> -----Original Message-----
> From: Edwin Martin
> Sent: Friday, March 26, 2004 11:25 AM
>

> +----------+-----------+--------+
> | naam     | relatienr | medium |
> +----------+-----------+--------+
> | De Boer  |       101 |      5 |
> | De Boer  |       101 |      0 |
> | Bakker   |       102 |      0 |
> | Bakker   |       102 |      3 |
> | De Groot |       103 |      4 |
> | De Groot |       103 |      0 |
> | Verkerk  |       104 |      5 |
> | Doornbos |       105 |      5 |
> | Kok      |       106 |      0 |
> | Kok      |       106 |      5 |
> +----------+-----------+--------+

> But... It doesn't work. For relatienr 102 and 103 it returns
> the medium 0, although there are mediums 3 and 4.

Edwin,

Let's take another look at your query:

> mysql> select naam, relatienr, max( medium ) from testje
> where medium in ( 0, 5 ) group by relatienr;

Keep in mind that the filter applied by the WHERE clause is applied before
the GROUP BY clause.  Therefore, only rows with the medium of 0 and 5 are
even considered.  For relatienr 102 and 103 there are only one row left for
each... and in both medium = 0, so the max is 0, naturally.

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168




More information about the thelist mailing list