select statement within decode

I had the following in access which basically calculated for every record whether it was the first record within any groupID (there would never be 2 records for the same group on the same date).

SELECT MyTable.GroupID, MyTable.record_date, IIf([record_date] In (select min([record_date]) from MyTable t where t.GroupID = [MyTable].[GroupID] group by t.GroupID),'First','Subsequent') as FirstOrSubsequent FROM MyTable

I've tried to reproduce it in Oracle using decode as below, but get the error 'ORA-00907: missing right parenthesis' every time I try to use a select statement for the expression.

select GroupID, record_date,
decode((record_date In (select min(record_date) from MyTable t where t.GroupID = MyTable.GroupID group by t.GroupID)),-1,'First',0,'Subsequent') as FirstOrSubsequent
from MyTable

I know I could probably do this with some kind of stored procedure, but the DB is on an FM contract so I can't write any procedures / temp tables etc.

Is it possible to use a SELECT within a decode, and if so, please could someone give me the correct syntax

TIA
Jeff Received on Thu Mar 23 2000 - 16:04:50 CST