I've tried this on MySQL 4.0.15 and 4.1.0-alpha, with
the same result.
I have the following table A:
+----+-------+------+
| id | value | type |
+----+-------+------+
| 1 | 6 | a |
| 2 | 2 | b |
| 3 | 5 | b |
| 4 | 4 | a |
| 5 | 1 | c |
| 6 | 10 | d |
| 7 | 7 | c |
| 8 | 3 | d |
+----+-------+------+
I would like to find the max of value for each type,
and the min of value for each type. For this, I ran
the following two queries (I'm not sure they are well
formulated for my goal):
select id, value, type from A group by type having
value = max(value);
+----+-------+------+
| id | value | type |
+----+-------+------+
| 1 | 6 | a |
| 6 | 10 | d |
+----+-------+------+
2 rows in set (0.00 sec)
select id, value, type from A group by type having
value = min(value);
+----+-------+------+
| id | value | type |
+----+-------+------+
| 2 | 2 | b |
| 5 | 1 | c |
+----+-------+------+
2 rows in set (0.00 sec)
In each case, the order in the table seems to
matter: if the max value is found first, it's
returned in the search for max query. Same goes for
min. For example, for type a, the maximum value comes
first in the table, so it's returned in the search
for max query. Again for case a, the minimum value
comes second in the table, so it's not found in the
search for min query ????
This seems odd. Can anyone tell me what I'm missing ?
Thanks,
Ana
=====
Ana Holzbach
anaholzbach@stripped
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
