How to add order by for Union result?

Question

In SQL Server 2008, we can use Union/Unino All to put two results together, but I want to add order by for the final result. How can do that?

What I want is something like:

select id1 as id, * from ...
Union All
select id2 as id, * from ...
order by id

Help please. Thanks.

Have you tried the above? Did it work? If not, what problems were you encountering? — Damien_The_Unbeliever, Dec 17 '10 at 14:18
@JNK - Quite the contrary - you're only allowed one `ORDER BY` on a `UNION (ALL)`, it has to be at the end, and it applies to the entire result set. — Damien_The_Unbeliever, Dec 17 '10 at 14:26
I run a similar query on sql 2008 without error. @KentZhou please show as your query which runs without order by, but fails with order by. — bernd_k, Dec 17 '10 at 14:32

score 12 · Answer 1 · answered Dec 17 '10 at 14:39

As noted in the comments, the pseudocode you've given should work, applying the order by to the combined result set. If you're trying to order in such a way that the two result sets are kept distinct, you'd need to introduce an artificial sort column, such as:

select id1 as id, *, 1 as MySortKey from ...
Union All
select id2 as id, *, 2 as MySortKey from ...
order by MySortKey, id

score 1 · Answer 2 · answered Jul 09 '14 at 09:27

1

You can use this query as well

select * from (
select id1 as id, * from ...
Union All
select id2 as id, * from ...
) aaaa
order by id

answered Jul 09 '14 at 09:27

NoNaMe

6,020
30
82
110

2

but why would you? – aaa90210 Oct 27 '16 at 21:57

score 0 · Answer 3 · edited May 23 '17 at 12:24

All of these answers included the original poster's attempt is just plain confusing or overly complicated. A simple and easy to follow explanation is here:

How to order by with union

with,

"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 " Just use UNION ALL instead of UNION here.

score 0 · Answer 4 · answered Dec 17 '10 at 14:41

0

use this code:

Select * from 
(select id1 as id, * from ...
Union All
select id2 ad id,  * from ...
order by id) as t
order by t.id

answered Dec 17 '10 at 14:41

ABI

1,714
15
14

I don't see how the outer query would change the results returned by the inner in any way. – Joe Stefanelli Dec 17 '10 at 14:43
@Joe: Perhaps if the first set isn't ordered? The outer order by could be useful if you're EXEC'ing sprocs that you're unsure of the returned order. – JasonCoder Dec 17 '10 at 14:49
@JasonCoder: The syntax can be confusing, but the order by in the inner query (which is the OP's original query) is actually applied to the combined result of the two select statements, not just to the second statement. – Joe Stefanelli Dec 17 '10 at 14:52

score 0 · Answer 5 · edited Apr 26 '11 at 01:02

0

use northwind

select OrderID, ProductID from [Order Details] a
UNION all
Select OrderID, EmployeeID from Orders b
order by a.ProductID

edited Apr 26 '11 at 01:02

CoolBeans

20,654
10
86
101

answered Dec 17 '10 at 14:57

Tendo Kaluma

1

How to add order by for Union result?

5 Answers5