CODEWITHSUNDEEP
sqlsql-order-byunion
Guilgamos
Guilgamos

Reputation: 3961

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.

Upvotes: 311

Views: 618527

Answers (12)

Mandrake
Mandrake

Reputation: 471

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 FROM 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 FROM Employee 
UNION
SELECT Customer_Name AS User_Name, Address FROM Customer
ORDER BY User_Name;

Upvotes: 31

osydorchuk
osydorchuk

Reputation: 143

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
)

Upvotes: 9

BA TabNabber
BA TabNabber

Reputation: 1356

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

Upvotes: 67

Edward
Edward

Reputation: 89

To add to an old topic, I used ROW_NUMBER (using MS SQL). This allows sorts (orders) within UNIONs. So using an idea from @BATabNabber to separate each half of the Union, and @Wodin of wrapping the whole thing in a select, I got:

Select Id, Name, Age from
(
Select Id, Name, Age, 1 as Mainsort
 , ROW_NUMBER() over (order by age) as RowNumber
From Student
Where Age < 15

Union

Select Id, Name, Age, 2 as Mainsort
 , ROW_NUMBER() over (Order by Name) as RowNumber
From Student
Where Name like '%a%'
) as x
Order by Mainsort, RowNumber

So adjust, or omit, what you want to Order by, and add Descendings as you see fit.

Upvotes: 4

ΩmegaMan
ΩmegaMan

Reputation: 31576

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 what you showed and places them into specific groups on the CTE; 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.

Upvotes: 2

Fandango68
Fandango68

Reputation: 4838

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.

Upvotes: -1

Daniel Ion
Daniel Ion

Reputation: 19

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);

Upvotes: 1

Yevgeny Simkin
Yevgeny Simkin

Reputation: 28349

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.

Upvotes: 15

Ahmad Aghazadeh
Ahmad Aghazadeh

Reputation: 17131

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

Upvotes: -1

Sunil Kumar
Sunil Kumar

Reputation: 665

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

Upvotes: 11

Mark Robinson
Mark Robinson

Reputation: 13278

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

Upvotes: 115

bernd_k
bernd_k

Reputation: 11966

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

Upvotes: 391

Related Questions