[thelist] MySQL "group by" problem

[Edwin Martin]

I'm confused. I thought I knew how GROUP BY works, but now 
I'm not sure.

I've a simple table:

mysql> select * from testje;
+----------+-----------+--------+
| 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 |
+----------+-----------+--------+

Now I want to have unique name-relatienr pairs with the 
highest medium.

So I thought out this query:

mysql> select naam, relatienr, max( medium ) from testje 
where medium in ( 0, 5 ) group by relatienr;
+----------+-----------+---------------+
| naam     | relatienr | max( medium ) |
+----------+-----------+---------------+
| De Boer  |       101 |             5 |
| Bakker   |       102 |             0 |
| De Groot |       103 |             0 |
| Verkerk  |       104 |             5 |
| Doornbos |       105 |             5 |
| Kok      |       106 |             5 |
+----------+-----------+---------------+

It groups the relatienr's and (should) return the highest 
medium.

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

My MySQL version is:
mysql  Ver 12.20 Distrib 4.0.13, for pc-linux-gnu (i686)

Who can help me with this?

Edwin Martin