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  

Answer 2

You don't really need to have parenthesis. You can sort directly:

SELECT *, 1 AS RN FROM TABLE_A UNION ALL  SELECT *, 2 AS RN FROM TABLE_B ORDER BY RN, COLUMN_1