check out the "connect by B = prior A start with A = <start value>" syntax. this enables you to do a tree walk that will automatically stop when there are no more child rows. Kind regards, Lex. ------------------------------- visit http://www.naturaljoin.nl <http://www.naturaljoin.nl> ------------------------------- skype me <callto://lexdehaan> -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of susan lam Sent: Thursday, September 09, 2004 20:27 To: oracle-l@xxxxxxxxxxxxx Subject: writing "recursive SQL" Hi, I would like to write a SQL to recursively extract data from a table. The algorithm is as follows: SQL> select * from mytable; A B ---------- ---------- 1 2 2 3 3 4 4 5 5 6 0 7 7 8 8 9 8 rows selected. If A=3, output the corresponding value of B (ie 4), then match B (ie 4) with A and output the next corresponding value of B (ie 5) and so on... Recursion stops when B != A A & B are unqiue and A=<value> is a user input value The result I'm looking for is: if A=3, then the output is: 4 5 6 if A=7, then the output is: 8 9 Is there a way to accomplish that in SQL? thanks. susan __________________________________ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe To search the archives - //www.freelists.org/archives/oracle-l/ -- Binary/unsupported file stripped by Ecartis -- -- Type: text/x-vcard -- File: Lex de Haan.vcf -- To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe To search the archives - //www.freelists.org/archives/oracle-l/