* 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]
