Thank you Bill and Lawrence I had no idea you could have the same table in a select more than once like that.
I never have used the exists keyword before either. Thanks a million, i wonder where else i can use these techniques (: ----- Original Message ----- From: "Bill Downall" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Friday, April 30, 2004 2:39 PM Subject: [RBASE-L] - Re: sql riddle > Alan, > > This requires two references to the table, each image getting a separate > correlation name so that your WHERE conditions can tell the images apart: > > SELECT tranid > FROM tranactions t1 + > WHERE t1.tranaction = 'WO Completed' + > AND EXISTS + > (SELECT tranID FROM tranactions t2 + > WHERE t2.tranaction = 'WO Approved' + > AND t1.tranactiondts < t2.tranactiondts) > > Bill > > > On 30 Apr 2004 at 14:16, Alan Wolfe wrote: > > > Hi everyone, > > > > I have something I'm trying to find the sql for but haven't so far > > been able to do it. > > > > we have a table called tranactions. it has 3 columns: > > > > tranid integer > > tranaction text 14 > > tranactiondts datetime > > > > heres an example of this table's data: > > > > 1234, 'Submit WO',04/29/2004 12:31:45 > > 1234, 'WO Completed', 04/29/2004 02:00:00 > > 1234, 'WO Approved',04/30/2004 06:46:44 > > 5678, 'Submit WO', 04/30/2004 12:48:46 > > 5678, 'WO Approved', 04/30/2004 2:13:36 > > 5678, 'WO Completed', 04/30/2004 3:14:42 > > > > What I'm trying to do is find out which tranids have a "WO Completed" > > that comes before the "WO Approved" comparing tranactiondts. > > > > so, for the above data, it would report that 1234 had "WO Completed" > > before it had "WO Approved", but it wouldnt report 5678 because > > completed comes after approved. > > > > Anyone have any idea how to do this? > > > > Thank you! > > Alan >
