How to add order by for Union result?

Question

In SQL Server 2008, we can use Union/Unino All to put two results together, but I want to add order by for the final result. How can do that?

What I want is something like:

select id1 as id, * from ...
Union All
select id2 as id, * from ...
order by id

Help please. Thanks.

Answer 1

As noted in the comments, the pseudocode you've given should work, applying the order by to the combined result set. If you're trying to order in such a way that the two result sets are kept distinct, you'd need to introduce an artificial sort column, such as:

select id1 as id, *, 1 as MySortKey from ...
Union All
select id2 as id, *, 2 as MySortKey from ...
order by MySortKey, id

Answer 2

You can use this query as well

select * from (
select id1 as id, * from ...
Union All
select id2 as id, * from ...
) aaaa
order by id