* 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
