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
> 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
> Jann Linder
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.