Hi Rick,
I understand your requirement now..
yup, my brain works at steam-engine speed. ;)

here is my solution:

write a SP with a Query like this:

FOR
select x,y,z,1 from table1
group by x,y,z -- you can delete this line if there is no dup. pos.
union all
select x,y,z,2 from table2
group by x,y,z -- you can delete this line if there is no dup. pos.
union all
select x,y,z,3 from table3
group by x,y,z -- you can delete this line if there is no dup. pos.
order by 1,2,3,4
into :x,:y,:z,:t

suppress the duplicate rows yourself,
(the duplicate possibility is between 1 and 3)
via comparing previous x,y,z values:

if(prev_x<>x or prev_y<>y or prev_z<>z)
then
begin
SUSPEND;
prev_x=x;
prev_y=y;
prev_z=z;
end



select x,y,z,t from myproc
order by t,x,y,z

Now, they are in order of you wanted and there is no duplicate.

Regards.
Ali

--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@r...>
wrote:

> 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