Menu
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?
326
The UNION ALL operator can use the ORDER BY clause to order the results of the query in SQL Server (Transact-SQL).
You can use UNION ALL to avoid sorting, but UNION ALL will return duplicates.
Within UNION each SELECT statement must have the same columns number. The columns must have analogous data types. In each SELECT statement, the columns must be in the same order.
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 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
