You need CASE with ORDER BY clause, try this:
with cte as (
select distinct
State_Code,
case when State_Code=' ' then 'Unknown'
else State_Code
end State_Code2,
1 as 'State_Code1'
from Dim_Prop_Geo_T
union
select 'All' as State_Code,
'All' ,
0 as 'orderby')
select State_Code, State_Code2, State_Code1
from cte
order by case when State_Code='All' then 0 else 1 end
... you may need to tweak the query for correct output.
For more info on using CASE with ORDER BY clause check this link: http://sqlwithmanoj.wordpress.com/?s=order+by+case