SQL Query - Using Order By in UNION

Question

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

Answer 1

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