Thanks. I ended up doing two queries inside the SP, the first with one select and the second with two.
The first select is for an exact match, the next two are for substitutions.
Given that most of the time I'll find an exact match and the caller will be able to use that exact match, it won't loop through the rest of the result set and thus the union of the next two more complicated queries won't be needed.
There was no need in this case to order between the last two queries, as all substitutions are equally good.

Rick DeBay

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Ali Gökçen
Sent: Tuesday, October 04, 2005 8:55 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Ordering of union results

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

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

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