UNION ALL and ordering

Giganews Newsgroups
Subject: UNION ALL and ordering
Posted by:  chrism7…@gmail.com
Date: Fri, 23 Nov 2007

When I UNION ALL two sql statements, I need the results from the first
statement to come back first in the result set.  So if I do something
like:

select col1, col2, ... from table1 order by col2
UNION ALL
select col1, col2, ... from table2 order by col3

can I always assume that the results will come back with table1 rows
first followed by the table2 results?  From some limited testing, this
appears to be the case, but is Oracle designed to work this way, or
have I just been lucky?

If this isn't the case, then I would have to add a pseudo column and
sort by it like this:

select 1, col1, col2, ... from table1 order by col2
UNION ALL
select 2, col1, col2, ... from table2 order by col3
ORDER BY 1

The problem with this approach, is that any order bys I do in the
individual select statements may no longer be valid because of the
"ORDER BY 1" for the UNION.

Replies