Re: UNION ALL and ordering

chrism778_at_gmail.com schrieb:
> 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.

As Arch already mentioned, what you suppose to do is not even valid sql in Oracle. You can apply only one final order by clause which rely to the whole result set. To be sure to have rows ordered like you wish (i.e. from the first select, then from the second) i am afraid, you have to take a pseudocolumn approach. You can however apply additional sorting criteria after ordering by <subset_marker>.

Best regards

Maxim Received on Fri Nov 23 2007 - 14:21:46 CST