SQL Query - Using Order By in UNION
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_ALIAS1UNION ALLSELECT * FROM( SELECT table2.field1 FROM table2 ORDER BY table2.field1) DUMMY_ALIAS2
SELECT field1 FROM table1UNIONSELECT field1 FROM table2ORDER BY field1
I think this does a good job of explaining.
The following is a UNION query that uses an ORDER BY clause:
select supplier_id, supplier_namefrom supplierswhere supplier_id > 2000UNIONselect company_id, company_namefrom companieswhere company_id > 1000ORDER BY 2;Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set.
In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".
The supplier_name / company_name fields are in position #2 in theresult set.
Taken from here: http://www.techonthenet.com/sql/union.php