UNION ALL and ordering

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. Received on Fri Nov 23 2007 - 08:06:40 CST