>Sorry, I made a mistake with my original SQL statement, it should have been:
>
>Select * from dbo.SystemDataDictionary union all select * from dbo.ApplicationDataDictionary order by DataID
>
>The clause I'm matching is SystemDataDictionary.DataField and ApplicationDataDictionary.DataField, where if the record exists in SystemDataDictionary and ApplicationDataDictionary I'd like to keep the result from SystemDataDictionary. If the record appears in either, but not both, then include that in the result set too.
>
>Regards
>
>ps - I'll say thank you and add a gold star too this time! :-)

The same original logic works exactly for your case.

Select * from dbo.SystemDataDictionary where DataField = @Field

union all select * from dbo.ApplicationDataDictionary where DataField = @Field 
and not exists (select 1 from dbo.SystemDataDictionary where DataField = @Field)
order by DataID

This logic satisfies your requirements.

Alternative solution

;with cte as (Select *, 1 as SortOrder from dbo.SystemDataDictionary where DataField = @Field
union all 
select *, 2 as SortOrder from dbo.ApplicationDataDictionary where DataField = @Field)

select * from (select *, row_number() over (order by SortOrder) as row from cte) X where Row = 1 order by DataID