Hi Rick,
I don't understand what are you trying to do..

union and union all work great for me.

select x,y,1 from blabla
union
select x,y,2 from blabla

does mean: hey don't eliminate duplicates rows, i gived a number to
make them unique!

if you give a rownumber to none-duplicated record set
then write this query in a stored procedure and give a row number
before suspend it.

create procedure myunionswithrownum
returns (x integer, y integer, z integer, rownum integer)
as
begin
rownum=0;

for select..
union
select...
union
select..
into :x,:y,:z;
do
begin
rownum=rownum+1;
suspend;
end

end

i didnt test it, so i have no idea if unions is available in cursors.

if not, then u can create view for it then use it in cursor.

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