Home » SQL & PL/SQL » SQL & PL/SQL » START WITH CONNECT BY
START WITH CONNECT BY [message #242581] Mon, 04 June 2007 03:36 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Hi,
I have a sample table:


create table tab(a number, b number);


insert into tab values(9, 8);
insert into tab values(9, 7);
insert into tab values(8, 6);
insert into tab values(8, 5);
insert into tab values(5, 4);
insert into tab values(4, 3);
insert into tab values(4, 2);
insert into tab values(4, 1);




I just wanna ask on using start with and connect by how can i get the final child/leaf of a parent? like for example if I give 9 as the parent or 8, it would return 1, 2, and 3. I have made a query, but I dont think this is the right way of doing it. Please see my query, thanks =)


SQL> select b
  2  from tab
  3  where a = (select min(a)
  4            from tab
  5            start with a = 9
  6            connect by prior b = a)
  7  /

         B
----------
         3
         2
         1

SQL> 




Regards,
Rhani
Re: START WITH CONNECT BY [message #242854 is a reply to message #242581] Tue, 05 June 2007 05:21 Go to previous messageGo to next message
techno
Messages: 44
Registered: October 2003
Member
The query

select min(b)
from ttt
start with a = 9
connect by prior b = a


itself will give you the final child for all the records having parent 9 and less

Re: START WITH CONNECT BY [message #242856 is a reply to message #242581] Tue, 05 June 2007 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
min only works because your leaves has the lower figures.
I should say it works by chance.

Regards
Michel
Re: START WITH CONNECT BY [message #242868 is a reply to message #242856] Tue, 05 June 2007 06:20 Go to previous messageGo to next message
techno
Messages: 44
Registered: October 2003
Member
Yes Michale.
I am wrong. It is not min() which decides the final child but the level of parent child relationship
the following query results

select b
from ttt
where level = (select max(lvl)
from (select level lvl from ttt
start with a = 9
connect by prior b = a))
start with a = 9
connect by prior b = a



There could be multiple child records with the final child hierarchy level

Regards
Techno
Re: START WITH CONNECT BY [message #242874 is a reply to message #242868] Tue, 05 June 2007 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Leaves may be at different levels.

Please format your query otherwise it is unreadable:
How to format your posts.

Regards
Michel
Re: START WITH CONNECT BY [message #242954 is a reply to message #242581] Tue, 05 June 2007 10:50 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
I'm not sure I understand your requirements entirely, but it sounds like the CONNECT_BY_ISLEAF function may be what you're after. There are some examples of this function in action at SQL Snippets: Hierarchical Data - Meta Data.

--
Joe Fuda
SQL Snippets
Re: START WITH CONNECT BY [message #242958 is a reply to message #242954] Tue, 05 June 2007 11:00 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sure this is what he needs.

Regards
Michel
Previous Topic: SQL Natural Join
Next Topic: Query
Goto Forum:
  


Current Time: Sun Dec 04 18:45:20 CST 2016

Total time taken to generate the page: 0.07403 seconds