Subject RE: [firebird-support] Re: Ordering of union results
Author Rick Debay
Thanks, but what I posted was a simple example. The actual query would probably choke the optimizer (or the next person to maintain it) if I tried to use NOT EXISTS :-)
If I could use Views it would be much easier to do, I anxiously await FB 2.0 so queries with views are properly planned.

Thanks, Rick DeBay

-----Original Message-----
From: [] On Behalf Of Svein Erling Tysvær
Sent: Saturday, October 01, 2005 5:53 PM
Subject: [firebird-support] Re: Ordering of union results

Hi Rick!

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

select a, 1
from tablea a1
select b, 2
from tableb b1
where not exists(select * from tablea a2 where b1.b = a2.a) union 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

------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back!


Visit and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at


Yahoo! Groups Links