SQL Query - Using Order By in UNION

ms-access, sorting, sql, sql-order-by, union

Solution

Sometimes you need to have the `ORDER BY` in each of the sections that need to be combined with `UNION`.

In this case

SELECT * FROM 
(
  SELECT table1.field1 FROM table1 ORDER BY table1.field1
) DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT table2.field1 FROM table2 ORDER BY table2.field1
) DUMMY_ALIAS2

Problem

How can one programmatically sort a union query when pulling data from two tables? For example, ``` SELECT table1.field1 FROM table1 ORDER BY table1.field1 UNION SELECT table2.field1 FROM table2 ORDER BY table2.field1 ``` Throws an exception Note: this is being attempted on MS Access Jet database engine

Original source