Yeah, that's what I'm going to have to do, but with only two because I
can't have any duplicates, and I can only supress one form of them in
the Where clause.
The order is there because each query produces better quality results
then the next one. But it's possible for queries two and three to
produce an identical row.

This would be a whole lot easier (drug inventories) if the
pharmaceutical industry didn't try to game the system :-(

Thanks, Rick DeBay

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Friday, September 30, 2005 7:49 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Ordering of union results

--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@r...>
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.?
>

Rick,

I would treat it as undetermined. There is no safe guaranteed order
unless you refer to it in the order by. Obviously there will be no
difference now between union and union all, because the field at the end
ensures they are all unique.

Btw, if there are duplicates, which the order they go in would have been
undefined if your removed it anyway. Could you possibly use a SP with 3
separate select statements to achieve this?

Adam




------------------------ 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