Merge Individual Query Result in a sorted fashion

It is not possible to apply Order by Clause while using UNION Query, when we want to sort the individual query result and then want to merge the result data

Case: If we want to sort the individual query result and then want to merge the result data it is not possible to do so. Read the following article to see how it is possible.

Example:

Problem
--------
select categoryid,categoryname
from categories
order by 2
UNION
select productid,productname
from products
order by 2

Error
------
--It will give me following error
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'UNION'.


Partial Solution
------------------
select categoryid,categoryname
from categories
UNION
select productid,productname
from products
order by 2

--Problem with Partial Solution
It will sort all the result data by column 2., It is general sorting which is applied when we want to sort all the result data.

But what if we want to sort the individual query result and then want to merge the result data.

Solution
----------
select * from
(
select categoryid,categoryname, 1 as myOrder
from categories
UNION
select productid,productname, 2 as myOrder
from products
)
myTable
order by myTable.myOrder

So finally here the result query will display the data sorted by category and then by product. and the result data is shown.