How to order by with union in SQL?

Question

Just write

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"
Order by name

the order by is applied to the complete resultset

Select id,name,age
from
(
   Select id,name,age
   From Student
   Where age < 15
  Union
   Select id,name,age
   From Student
   Where Name like "%a%"
) results
order by name

In order to make the sort apply to only the first statement in the UNION, you can put it in a subselect with UNION ALL (both of these appear to be necessary in Oracle):

Select id,name,age FROM 
(    
 Select id,name,age
 From Student
 Where age < 15
 Order by name
)
UNION ALL
Select id,name,age
From Student
Where Name like "%a%"

Or (addressing Nicholas Carey's comment) you can guarantee the top SELECT is ordered and results appear above the bottom SELECT like this:

Select id,name,age, 1 as rowOrder
From Student
Where age < 15
UNION
Select id,name,age, 2 as rowOrder
From Student
Where Name like "%a%"
Order by rowOrder, name

As other answers stated, ORDER BY after the last UNION should apply to both datasets joined by union.

I had two datasets using different tables but the same columns. ORDER BY after the last UNION still didn't work.

Using an alias for the column used in the ORDER BY clause did the trick.

Select Name, Address for Employee 
Union
Select Customer_Name, Address from Customer
order by customer_name;   --Won't work

The solution was to use the alias User_Name, shown below:

Select Name as User_Name, Address for Employee 
Union
Select Customer_Name as User_Name, Address from Customer
order by User_Name; 

Both other answers are correct, but I thought it worth noting that the place where I got stuck was not realizing that you'll need order by the alias and make sure that the alias is the same for both the selects... so

select 'foo'
union
select item as `foo`
from myTable
order by `foo`

notice that I'm using single quotes in the first select but backticks for the others.

That will get you the sorting you need.

Order By is applied after union, so just add an order by clause at the end of the statements:

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like '%a%'
Order By name

If I want the sort to be applied to only one of the UNION if use UNION ALL:

Select id,name,age
From Student
Where age < 15
Union all
Select id,name,age
From 
(
Select id,name,age
From Student
Where Name like "%a%"
Order by name
)