RE: question on hierarchical query

Hi,

This should satisfy your requirements:

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

HTH, Alan Davey

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]=  On Behalf Of susan lam
Sent: Friday, October 15, 2004 8:30 AM
To: oracle-l_at_freelists.org
Subject: question on hierarchical query

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

OR

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 manner.=0D
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 query?

thanks.

susan

        =0D

--

http://www.freelists.org/webpage/oracle-l

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

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 15 2004 - 07:46:13 CDT