* 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


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to