Hi Roger, Thanks for the pointer.
CONCAT would be a nightmare to maintain, especially with data where the values can have all sorts of ranges, and where we could just as well be looking for the value corresponding to the latest (or earliest) date instead. It would just create too many cases to pad, parse, etc. However, I tried the substring on 4.1 and it's a reasonable alternative. Fortunately, by the time we need this functionality 4.1 will probably be stable. Ana --- Roger Baklund <[EMAIL PROTECTED]> wrote: > * 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 ===== 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]
