Jann Linder wrote:
>
> I have a table whose columns are "orderno, status, date" in the table
> 'status' ( i know...table AND column names are not good to be the same)
>
> for every orderno, the table can have many many entries depending on the
> current status.
>
> What i wish to do is the following:
>
> grab the MOST RECENT status for EACH ORDERNO - and - be able to also grab
> all order numbers whose status is 'new'. Status being defined as the MOST
> RECENT ENTRY for each orderno.
>
> I tried what i thought would work which is:
>
> select status.orderno from status s where s.status='new' and
> s.entrydate=(select MAX(t.entrydate) from status t where
> t.orderno=s.orderno);
>
> But it died on the =(select MAX...
>
> portion. Any idea on how to do it?
>
> HLP PLZ as i am forced to do it in perl curently and it takes 10-15 min. to
> run.
>
> Jann Linder
Hi Jann
There was another thread on this this week.
Please look in the mailinglist archive yourself.
To summarize it in short:
You can't do this.
You first have to get the MAX(...) values, and then get the corresponding entries.
Tschau
Christian
