Greg Coladonato wrote:
>
> > Hi Jules
> >
> > The second one doesn't work!
> > This is, because the Grouping is done on the resultset of the WHERE
> > part. So there will be the first t2.else which is found and not
> > the one belonging to the MAX(..) part.
> >
> > So the only way to do this is:
> > 1) generate temp table:
> > CREATE tempTable (id int, date DATE);
> > 2) fill it from the original table:
> > INSERT INTO tempTable SELECT id, MAX(date) FROM origTable WHERE
> > id='such-and-such' GROUP BY id;
> > 3) get the desired data:
> > SELECT o.id, o.date, o.else FROM origTable AS o, tempTable AS t
> > WHERE o.id = t.id AND o.date = t.date;
> > 4) garbage collect:
> > DROP tempTable;
> >
> > Tschau
> > Christian
>
> There's one more wrinkle: if there is more than one row in the
> original table for a particular id and max(date), then you'll need to
> apply an aggregate function to the 'else' column as well. That is,
>
> > SELECT o.id, o.date, o.else FROM origTable AS o, tempTable AS t
> > WHERE o.id = t.id AND o.date = t.date;
>
> might return more rows than are in tempTable if (id,date) is not
> unique in origTable.
>
> Greg
Hi Greg
You are absolutely right here, but if you just need any of the possible values, then you
can use another "GROUP BY o.id, o.date" in SELECT 3)
Tschau
Christian
