RE: question on hierarchical query

  • From: "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
  • To: <susanzlam@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 15 Oct 2004 07:37:49 -0500


This should satisfy your requirements:

select distinct * from hier
connect by prior b =3D a
start with a >=3D 100


Alan Davey

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]=
 On Behalf Of susan lam
Sent: Friday, October 15, 2004 8:30 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: question on hierarchical query

I'm having trouble constructing a hierarchical query.
Can someone help.

SQL> select * from hier;
         A          B
---------- ----------
        99          7
       100          1
       100          2
         1          2
         2          3
         4          8
       200          6

7 rows selected.

The order of the output is not important. The output
can be:
100     1
1       2
2       3
100     2
2       3      -> should not display this duplicate
200     6


100     1
100     2
1       2
2       3
2       3      -> should not display this duplicate
200     6

We will start with a number. In this case, 100. If
a=3D100, get the corresponding value of b and match that
back to a and get the next corresponding value of b
until b!=3Da. After the first iteration, now get the
next value of a>=3D100 and process it in the same
Duplicate records should only be displayed once, in
this case, 2 3 should only be displayed once. =0D

Is it possible to achieve the requirements in a single





"This information in this e-mail is intended solely=0D
for the addressee and may contain information=0D
which is confidential or privileged.  Access to this
 e-mail by anyone else is unauthorized.  If you=0D
are not the intended recipient, or believe that=0D
you have received this communication in error,=0D
please do not print, copy, retransmit,=0D
disseminate, or otherwise use the information.=0D
Also, please notify the sender that you have=0D
received this e-mail in error, and delete the=0D
copy you received."

Other related posts: