Subject Re: Ordering of union results
Author Adam
--- In, "Rick Debay" <rdebay@r...> wrote:
> I have three queries that I aggregate with a union. I'd like the
> results from the first query placed before the second, and the second
> before the third. I accomplished this by adding a number to the end of
> the results for each query, and ordering by that column:
> Select x,y,z,1
> From query1
> Union
> Select x,y,z,2
> From query2
> Union
> Select x,y,z,3
> From query3
> Order by 4
> This worked, but duplicates are no longer surpressed. If I remove the
> order by clause, will the results be ordered by their order around the
> union predicates, or is it undetermined.?


I would treat it as undetermined. There is no safe guaranteed order
unless you refer to it in the order by. Obviously there will be no
difference now between union and union all, because the field at the
end ensures they are all unique.

Btw, if there are duplicates, which the order they go in would have
been undefined if your removed it anyway. Could you possibly use a SP
with 3 separate select statements to achieve this?