How Can I Order Results from Multiple UNIONed SQL Queries?

Sorting Results from Combined SQL Queries using UNION

When using UNION in SQL to combine results from multiple SELECT statements, you might need to sort the final output. Understanding how ordering works with UNION is crucial.

Consider this example:

<code class="language-sql">SELECT id, name, age
FROM Student
WHERE age < 20
UNION
SELECT id, name, age
FROM Student
WHERE age > 30;</code>

Simply adding an ORDER BY clause to each individual SELECT statement will not order the final combined result. The ORDER BY clauses within each subquery are only relevant to that specific subquery's results before they are combined.

To correctly sort the combined result set, place the ORDER BY clause after the final UNION statement:

<code class="language-sql">SELECT id, name, age
FROM Student
WHERE age < 20
UNION
SELECT id, name, age
FROM Student
WHERE age > 30
ORDER BY name;</code>

In this revised query, the ORDER BY name clause applies to the entire dataset produced by the UNION operation. The combined results are treated as a single table, and then sorted alphabetically by the name column. This ensures proper ordering regardless of which original subquery contributed the data.

The above is the detailed content of How Can I Order Results from Multiple UNIONed SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!