* Ana Holzbach
> 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 ?
You are using a special form of the GROUP BY, not standard but accepted by
mysql. Try changing it to a more standard form, and you can combine both
queries in one:
SELECT type,MIN(value),MAX(value)
FROM A
GROUP BY type
--
Roger
