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 <roger@stripped> 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
anaholzbach@stripped
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
