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