most databases support the 'exists' functionality, so you could do something like this:
select a.unittype from
unit a where
( a.unittype = 'S3' OR
( a.unittype = '**' and not exists ( select b.unittype from unit b where b.unittype = 'S3' and effectivedate > somedate ) ) )
AND effectivedate > somedate
something like that anyways
Jamie