How Can I Correctly Use ORDER BY in UNION Queries to Avoid Errors?

Implement Order By

in UNION query

In database programming, a UNION query combines results from different tables or subqueries. However, using ORDER BY directly in a UNION statement may cause exceptions when data is extracted from multiple tables and the results need to be sorted. As shown in the following example:

SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY table2.field1

This query will fail, especially when using the MS Access Jet database engine. The solution lies in enclosing each subquery in its own ORDER BY clause:

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

The ORDER BY statement will be applied to each subquery individually by enclosing the subquery in parentheses and assigning it an alias (DUMMY_ALIAS1 and DUMMY_ALIAS2). This ensures correct ordering of the combined results without causing errors.

The above is the detailed content of How Can I Correctly Use ORDER BY in UNION Queries to Avoid Errors?. For more information, please follow other related articles on the PHP Chinese website!