Some quick SQL help on hard query

Hi guys,
I am trying to write a query that in one of the where columns there is a default value and it's giving me trouble.
Basically, I narrow a table of records down by a few different fields. One of the fields is a "unittype" field. There's a know set of values in the table, so I set a value to check like unittype = 'S3', but there are also records in the same column like '**' which stands for default.
The table has an "effectivedate" colum, so I set up a where effectivedate > "somedate" and unittype = 'S3'.
My problem is this, what I really want is where unittype = 'S3' or if there is no 'S3' use the '**' record.
Also if there were both an 'S3' and '**' that met the other criteria, I can only have one of the records returned, because this is a subquery. I would want the 'S3' always first and the '**' only if no 'S3' existed that met the effective date requirement.
So I need some help! how to I write the where statement so that it favors the 'S3', but still inserts the '**' record if it can't find 'S3'?
Thanks a million!

A

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