[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