[sql] How to order by with union in SQL?

Is it possible to order when the data is come from many select and union it together? Such as

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

How can I order this query by name?

I tried this

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

But that does not work.

Why not use TOP X?

SELECT pass1.* FROM 
 (SELECT TOP 2000000 tblA.ID, tblA.CustomerName 
  FROM TABLE_A AS tblA ORDER BY 2) AS pass1
UNION ALL 
SELECT pass2.* FROM 
  (SELECT TOP 2000000 tblB.ID, tblB.CustomerName 
   FROM TABLE_B AS tblB ORDER BY 2) AS pass2

The TOP 2000000 is an arbitrary number, that is big enough to capture all of the data. Adjust as per your requirements.

Add a column to the query which can sub identify the data to sort on that.

In the below example I use a Common Table Expression with the selects you showed which places them in specific groups in the CTE, and then do a union off of both of those groups into AllStudents.

The final select will then sort AllStudents by the SortIndex column first and then by the name such as:

WITH Juveniles as
(
      Select 1 as [SortIndex], id,name,age From Student
      Where age < 15
),

AStudents as
(
      Select 2 as [SortIndex], id,name,age From Student
      Where Name like "%a%" 
),

AllStudents as
(
      select * from Juveniles
      union 
      select * from AStudents
)

select * from AllStudents
sort by [SortIndex], name;

To summarize, it will get all the students which will be sorted by group first, and subsorted by the name within the group after that.

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

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

Can use this:

Select id,name,age
From Student
Where age < 15
Union ALL
SELECT * FROM (Select id,name,age
From Student
Where Name like "%a%")

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

To apply an ORDER BY or LIMIT clause to an individual SELECT, parenthesize the SELECT and place the clause inside the parentheses:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

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

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
)

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.

As other answers stated , 'Order by' after LAST Union should apply to both datasets joined by union.

I was having two data sets but using different tables but same columns. 'Order by' after LAST Union didn't still worked. Using ALIAS for column used in 'order by' did the trick.

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

So solution is use Alias 'User_Name' :

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