How to use order by with union all in sql?

sql, sql-server

Solution

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, then you can add a user defined 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

Problem

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?

Original source

Related problems