Sorting Numerically, And Alpha Numerically

Hi,

I'm hoping there is an easy way to deal with this....

I have a UNION query. The first SELECT in the UNION I want sorted by an int, the the second SELECT in the union (same column) I want sorted by a char.

Here's a really basic sample of what I mean:

select dummy_data.test from
(select cast(1 as char) as test
union
select cast(11 as char) as test
union
select cast(2 as char) as test
union
select 'Alpha' as test
union
select 'Bravo' as test) dummy_data
order by test asc

..obviously, I need to cast the INT columns as chars or the UNIONs do not work.

However, the sort will sort the numbers alpha numerically: 1,11,2
(I'm looking for 1,2,11)

Any thoughts?

david.