Re: distinct on a union query.

On Thu, 17 Jan 2002 10:03:44 -0500, "RickD." <rick_at_NOSPAMMEcanada.com> wrote:

>Hi folks,
>
>Can anyone help me?
>
>I'm trying to do a Select Distinct on this UNION query WHILE keeping the
>original ORDER.
>
>This:
>SELECT org.org_id, '1' || do.opp_doc_no AS Ord
>FROM document_order do, opportunity_document od,
> order_basket ob, document_distributor dd,
> organization org
>WHERE do.order_no = '1774021' AND do.doc_qty > 0 AND
> od.opp_no = do.opp_no AND
> od.buyer_cont_no = do.buyer_cont_no AND
>UNION
>SELECT org.org_id, '2' || ao.opp_att_no AS Ord
>FROM attachment_order ao, opportunity_attachment oa,
> order_basket ob, attachment_distributor ad,
> organization org
>WHERE ao.order_no = '1774021' AND ao.attmt_qty > 0 AND
> oa.opp_no = ao.opp_no AND
> oa.buyer_cont_no = ao.buyer_cont_no AND
>ORDER BY Ord ASC
>
>returns:
>60006 10
>123456 11
>32123 22
>60006 21
>
>What I want:
>60006 (actually the record with the 10 in it)
>123456
>32123
>
>TIA
>
>Rick.
>

an UNION is a set. A set is unique by design. Hence the result is correct, you get what you ask
There is no other solution than
select distinct leading column
from
(your orginal query)

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jan 17 2002 - 11:36:21 CST