How to Order Results from Multiple SELECT Queries Combined with UNION?

Sorting Results from Combined SELECT and UNION Queries

Combining multiple SELECT queries using UNION presents a common challenge: how to sort the resulting dataset. Let's explore this issue and its solution.

The Challenge

When retrieving data from various tables or using multiple SELECT statements joined by UNION, achieving a specific column sort order can be tricky.

Detailed Explanation

Consider this scenario:

SELECT id, name, age
FROM Student
WHERE age < 20
UNION
SELECT id, name, age
FROM Student
WHERE age >= 20;

This query selects data from two distinct groups within the Student table. However, a simple ORDER BY name clause placed within either SELECT statement won't correctly sort the combined results.

The Solution

The correct approach is to apply the ORDER BY clause after the UNION statement. The following query demonstrates the proper method:

SELECT id, name, age
FROM Student
WHERE age < 20
UNION
SELECT id, name, age
FROM Student
WHERE age >= 20
ORDER BY name;

This ensures that the ORDER BY clause operates on the complete dataset produced by the UNION operation, guaranteeing the desired sorted output.

The above is the detailed content of How to Order Results from Multiple SELECT Queries Combined with UNION?. For more information, please follow other related articles on the PHP Chinese website!