RE: Corrected SQL Question...

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 13 Mar 2003 10:54:01 -0800
Message-ID: <F001.005695F8.20030313105401@fatcity.com>

(see answer below)

> -----Original Message-----
> From: Deshpande, Kirti [mailto:kirti.deshpande_at_verizon.com]
>
> Here is the test data:
>
> SQL> select c1,c2 from cp;
>
> C1 C2
> --- ---
> AUS DAL
> AUS HOU
> DAL AUS
> DAL HOU
> DAL LIT
> DAL XYZ
> HOU AUS
> HOU DAL
> HOU LIT
> HOU XYZ
> LIT DAL
>
> C1 C2
> --- ---
> LIT HOU
> XYZ DAL
> XYZ HOU
>
> 14 rows selected.
>
> SQL>
>
> Here is what is required:
>
> C1 C2
> --- ---
> AUS DAL
> DAL AUS
> AUS HOU
> HOU AUS
> DAL HOU
> HOU DAL
> DAL LIT
> LIT DAL
> DAL XYZ
> XYZ DAL
> HOU LIT
> LIT HOU
> HOU XYZ
> XYZ HOU
This is not very elegant, but it works:
SQL> select * from cp ;
C1 C2
--- ---
AUS DAL
AUS HOU
DAL AUS
DAL HOU
DAL LIT
DAL XYZ
HOU AUS
HOU DAL
HOU LIT
HOU XYZ
LIT DAL
LIT HOU
XYZ DAL
XYZ HOU
14 ligne(s) sélectionnée(s).

SQL> -- desired result
SQL> select
  2 least (a.c1, a.c2) || greatest (a.c1, a.c2) as sort_field, a.c1, a.c2
  3 from cp a
  4 where not exists

  5     (select * from cp b
  6       where b.c2 = a.c1 and b.c1 = a.c2 and a.c1 > b.c1)

  7 union
  8 select
  9 least (c.c1, c.c2) || greatest (c.c1, c.c2) as sort_field, d.c1, d.c2  10 from
 11 cp c, cp d
 12 where
 13 c.c1 = d.c2 and c.c2 = d.c1 and c.c1 < d.c1  14 order by 1, 2 ;

SORT_F C1 C2
------ --- ---
AUSDAL AUS DAL
AUSDAL DAL AUS
AUSHOU AUS HOU
AUSHOU HOU AUS
DALHOU DAL HOU
DALHOU HOU DAL
DALLIT DAL LIT
DALLIT LIT DAL
DALXYZ DAL XYZ
DALXYZ XYZ DAL
HOULIT HOU LIT
HOULIT LIT HOU
HOUXYZ HOU XYZ
HOUXYZ XYZ HOU
14 ligne(s) sélectionnée(s).

create table cp
(c1 varchar2 (3), c2 varchar2 (3)) ;

insert into cp values ('AUS', 'DAL') ;
insert into cp values ('AUS', 'HOU') ;
insert into cp values ('DAL', 'AUS') ;
insert into cp values ('DAL', 'HOU') ;
insert into cp values ('DAL', 'LIT') ;
insert into cp values ('DAL', 'XYZ') ;
insert into cp values ('HOU', 'AUS') ;
insert into cp values ('HOU', 'DAL') ;
insert into cp values ('HOU', 'LIT') ;
insert into cp values ('HOU', 'XYZ') ;
insert into cp values ('LIT', 'DAL') ;
insert into cp values ('LIT', 'HOU') ;
insert into cp values ('XYZ', 'DAL') ;
insert into cp values ('XYZ', 'HOU') ;

commit ;
select * from cp ;
-- desired result
select

   least (a.c1, a.c2) || greatest (a.c1, a.c2) as sort_field, a.c1, a.c2  from cp a
 where not exists
   (select * from cp b
     where b.c2 = a.c1 and b.c1 = a.c2 and a.c1 > b.c1) union
select
  least (c.c1, c.c2) || greatest (c.c1, c.c2) as sort_field, d.c1, d.c2   from
    cp c, cp d
  where
    c.c1 = d.c2 and c.c2 = d.c1 and c.c1 < d.c1 order by 1, 2 ;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Mar 13 2003 - 12:54:01 CST