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

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