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: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Saturday, October 01, 2005 5:53 PM
To: firebird-support@yahoogroups.com
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
union
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

HTH,
Set

--- In firebird-support@yahoogroups.com, "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!
http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links