* Ana Holzbach > 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 ?
See the MAX-CONCAT trick: <URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html > It works with MIN() too, of course: select min(concat(lpad(value,6,'0'),'-',date)), type 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]
