Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: UNION ALL and ordering

Re: UNION ALL and ordering

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 23 Nov 2007 21:21:46 +0100
Message-ID: <4747365A.6050409@gmail.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US