Re: Union query, sort of

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Wed, 13 Apr 2005 06:20:01 +0000 (UTC)
Message-ID: <d3idmg$6af$1@klatschtante.init7.net>

On 2005-04-13, Randy Harris <notvalid_at_no.spam> wrote:
> Is it possible to Union on less than all of the columns?
>
> SELECT col1, col2, col3
> FROM table1
> UNION
> SELECT col1, col2, col3
> FROM table2
>
> Will give me all of the distinct records from both queries (no duplicates).
> But what I need is all of the records where (col1, col2) are unique. If
> they match between tables, but col3 doesn't, then include only the record
> from table1. Is that possible in Oracle 8.1.7.4?
>
> table1
> house blue 3
> boat green 5
> pool yellow 6
>
> table2
> house blue 3
> boat green 2
> pool white 6
>
> desired output:
> house blue 3
> boat green 5
> pool yellow 6
> pool white 6

Use analytical functions:

create table tb1 (

  col_1 varchar2(6),
  col_2 varchar2(6),
  col_3 varchar2(6)

);

create table tb2 (

  col_1 varchar2(6),
  col_2 varchar2(6),
  col_3 varchar2(6)

);

insert into tb1 values ('house', 'blue',   3);
insert into tb1 values ('boat',  'green',  5);
insert into tb1 values ('pool',  'yellow', 6);

insert into tb2 values ('house', 'blue',   3);

insert into tb2 values ('boat', 'green', 2); insert into tb2 values ('pool', 'white', 6);

select col_1, col_2, col_3 from (
  select
    row_number() over (partition by col_1, col_2 order by w) r,     col_1, col_2, col_3
  from (
    select col_1, col_2, col_3, '1st' w from tb1       union all
    select col_1, col_2, col_3, '2nd' w from tb2   )
)
where r = 1;

hth,
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Wed Apr 13 2005 - 01:20:01 CDT