# In SQL, how do you order by using a union?

## Problem

Is it possible to arrange data that comes from many selects and combine them? as an example

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

Is there a way to sort this query by name?

I tried this

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

That, however, does not function.

Asked by Guilgamos

## Solution #1

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 parameter is applied to the entire resultset.

Answered by bernd_k

## Solution #2

``````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
``````

Answered by Mark Robinson

## Solution #3

You can place the sort in a subselect with UNION ALL (both of these appear to be required in Oracle) to have it just apply to the first statement in the UNION:

``````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%"
``````

Alternatively, (in response to Nicholas Carey’s comment), you can ensure that the top SELECT is sorted and that the results appear above the bottom SELECT as follows:

``````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
``````

Answered by BA TabNabber

## Solution #4

ORDER Via following the last UNION should apply to both datasets connected by union, as described in earlier responses.

k.

The solution was to use an alias for the column in the ORDER BY clause.

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

The following is an example of how to utilize the 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;
``````

Answered by Mandrake

## Solution #5

Both other answers are accurate, but I felt it was worth mentioning that I got stuck since I didn’t realize you have to order by alias and make sure the alias is the same for both choices… thus

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

Notice that in the first choice, I’m using single quotes, while in the others, I’m using backticks.

This will provide you with the necessary sorting.

Answered by Yevgeny Simkin

Post is based on https://stackoverflow.com/questions/4715820/how-to-order-by-with-union-in-sql