Skip to Main Content
  • Questions
  • How to place UNION queries in specific order

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: January 19, 2017 - 9:00 pm UTC

Last updated: January 20, 2017 - 4:35 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

I have joined 4 queries with UNION and the results are not in ascending order. How can I put them in specific order from Query 1-4?


Here are the queries:

Select count(X) "Query1"
from Dual

UNION

Select count(X) "Query2"
from Dual

UNION

Select count(X) "Query3"
from Dual
UNION

Select count(X) "Query4"
from Dual


The order came out as follow:

Query3
Query4
Query2
Query1


Thanks in advance!

and Chris said...

If you want results in a particular order, you must use an order by! To ensure that the rows from the first union appear before the second, which are both before the third etc., add a column saying which order you want them. Then order by that:

select 1 q, 'query 1' d from dual
union
select 2 q, 'query 2' d  from dual
union
select 3 q, 'query 3' d  from dual
union
select 4 q, 'query 4' d  from dual
order  by q;

Q  D        
1  query 1  
2  query 2  
3  query 3  
4  query 4  

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to specify order in UNION

A reader, January 20, 2017 - 2:45 pm UTC

Thanks Chris. This works, however, I only need the results in column D. Reason being is that I will use this query in MS Query in Excel where the results will be exported into designated cells. I do not need the extra sequent column Q. Anyway to make that invisible?
Chris Saxon
January 20, 2017 - 4:35 pm UTC

Just don't select it, see the example in the review below.

hum

gh, January 20, 2017 - 4:05 pm UTC

select d from
(
select 1 q, 'query 1' d from dual
union
select 2 q, 'query 2' d from dual
union
select 3 q, 'query 3' d from dual
union
select 4 q, 'query 4' d from dual
order by q;

)
Chris Saxon
January 20, 2017 - 4:35 pm UTC

Thanks.