sql server - How to use order by with union all in sql?

Question

I tried the sql query given below:

SELECT * FROM (SELECT * 
FROM TABLE_A ORDER BY COLUMN_1)DUMMY_TABLE
UNION ALL 
SELECT * FROM TABLE_B 

It results in the following error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

I need to use order by in union all. How do I accomplish this?

Answer 1

SELECT  * 
FROM 
        (
            SELECT * FROM TABLE_A 
            UNION ALL 
            SELECT * FROM TABLE_B
        ) dum
-- ORDER BY .....

but if you want to have all records from Table_A on the top of the result list, the you can add user define value which you can use for ordering,

SELECT  * 
FROM 
        (
            SELECT *, 1 sortby FROM TABLE_A 
            UNION ALL 
            SELECT *, 2 sortby FROM TABLE_B
        ) dum
ORDER   BY sortby