List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:August 4 1999 8:30pm
Subject:Re: how to rewrite this into mysql-savvy select? HLP PLZ EMERGENCY...!
View as plain text  
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

Thread
how to rewrite this into mysql-savvy select? HLP PLZ EMERGENCY...!Jann Linder4 Aug
  • Re: how to rewrite this into mysql-savvy select? HLP PLZ EMERGENCY...!Christian Mack5 Aug