Recover Open Orders from Event Log

[gatea]

I have a table that is an event log of order information. Each order may
have multiple entries - Placed, Acknowledged, Filled, Cancelled, Complete.

I'd like to recover all records for a particular order only if it is not
COMPLETE. Table looks as follows:

create table orders (
EventTime datetime NOT NULL,
SeqNum int(11) NOT NULL auto_increment,
EventType varchar(32) default NULL,
OrderID varchar(32) default NULL,
<Lots of Other Columns>,
PRIMARY KEY (SeqNum),
KEY EVENT_TMST (EventTime)
);

In the following table, I'd like to recover Order1 (i.e., rows with SeqNum
1 thru 4). Order2 (i.e., SeqNum 13 thru 20) is completed so I don't need
to recover it.

EventTime SeqNum EventType OrderID <Lots of Other Columns>
------------ ------ ----------- ------- -----------------------
9/8/08 12:26 1 New Order1 . . .
9/8/08 12:26 2 Placed Order1
9/8/08 12:26 3 Acknowledged Order1
9/8/08 12:26 4 PartialFill Order1
9/8/08 13:19 13 New Order2
9/8/08 13:19 14 Placed Order2
9/8/08 13:19 15 Acknowledged Order2
9/8/08 13:19 16 Our-ACK Order2
9/8/08 13:19 17 Cancel Order2
9/8/08 13:19 18 Acknowledged Order2
9/8/08 13:19 19 Our-ACK Order2
9/8/08 13:19 20 COMPLETE Order2

What query would return rows 1 thru 4? We had this working on Oracle using
the Oracle 'minus' SQL construct. Doesn't seem to work on MySQL.

Any help is appreciated.


Thank you!

John
Central NJ

 

[IanWaterman]

select o.* from orders 0
innerjoin
(select orderid, maximum(case when eventype = 'COMPLETE' then 1 else 0 end) Completed
from orders
group by orderid) c
on (o.orderid = c.orderid and c.completed <> 1)

Ian