Roger, Thanks for your reply. Here's the next step: I've added a date column to my table as follows:
+----+-------+------+------------+ | id | value | type | date | +----+-------+------+------------+ | 1 | 6 | a | 2002-09-08 | | 2 | 2 | b | 2003-10-01 | | 3 | 5 | b | 2001-02-18 | | 4 | 4 | a | 1999-11-30 | | 5 | 1 | c | 2000-03-12 | | 6 | 10 | d | 1998-07-11 | | 7 | 7 | c | 2002-09-15 | | 8 | 3 | d | 2003-05-28 | +----+-------+------+------------+ Now I'd like to get the min value for the type, and the date where the min value occurred. So I tried the following: select min(value), type, date from A group by type; +------------+------+------------+ | min(value) | type | date | +------------+------+------------+ | 4 | a | 2002-09-08 | | 2 | b | 2003-10-01 | | 1 | c | 2000-03-12 | | 3 | d | 1998-07-11 | +------------+------+------------+ You can see that the min value is correct, but the date is just the first date found for the type on the table, which is not the intended result. Similarly for the max -- correct max values, first date found on the table for each type. Any suggestions ? >* 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 ===== Ana Holzbach [EMAIL PROTECTED] __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
