Subject Re: Ordering of union results
Author Svein Erling Tysvær
Hi Rick!

You could use NOT EXISTS in the latter queries to avoid duplicates,

select a, 1
from tablea a1
select b, 2
from tableb b1
where not exists(select * from tablea a2 where b1.b = a2.a)
select c, 3
from tablec c1
where not exists(select * from tablea a3 where c1.c = a3.a)
and not exists(select * from tableb b2 where c1.c = b2.b)
order by 2


--- In, "Rick Debay" 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.?
> Thanks, Rick DeBay