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
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@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
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
--
//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."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
