ORDER BY bug?

[ColinM]

ORDER BY requires the EXACT same text in the first select statement as in the ORDER BY clause.
The following query works

use pubs
select state,(case state when 'TX' then 1 else 2 end) from stores
union all
select state,(case state when 'TX' then 1 else 2 end) from publishers
order by (case state when 'TX' then 1 else 2 end)

However if the 2 is changed to a 3 in the first line it no longer works.

Anybody know a way round this?
As I wish to use order by in a XML query so I can't add an extra column in the select without changing the whole structure

 

[essa2000]

I am sure it will work :

select state, 'StateID' =
(case state when 'TX' then 1 else 3 end) from stores
union all
select state, 'StateID' =
(case state when 'TX' then 1 else 2 end) from publishers
order by StateID

essa2000