Home » SQL & PL/SQL » SQL & PL/SQL » Help regarding how to find length of chain in SQL (Oracle 10g)
Help regarding how to find length of chain in SQL [message #362009] Sat, 29 November 2008 19:00 Go to next message
floverboy
Messages: 4
Registered: November 2008
Junior Member
i have a table like below

COURSEID HAS_PREREQ
100 110
100 155
110
120 130
130 160
140 150
150 170
155
160 170
160 180
170
180

Quote:
i want a answer with attribute "Chainlength" which have value of chain like

the courseid 140 is having prerequisite 150 and 150 is having a prerequisite 170 and 170 have no prerequisites.so the tuple having courseid 140 must have "Chainlength" attribute value as 2.

the courseid 100 is having two prerequisite 110 and 155. but in turn 110 and 155 have no prerequisites. so courseid 100 must have attribute "Chainlength" value as 1


i got a hint to use "CONNECT BY". but i am not sure which CONNECT BY i have to use.

please help me out.
Re: Help regarding how to find length of chain in SQL [message #362012 is a reply to message #362009] Sat, 29 November 2008 21:21 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Please read the Forum Guidelines on how to format your post and provide the necessary create/insert statements.

In addition, what have you tried to solve the problem ?
Re: Help regarding how to find length of chain in SQL [message #362022 is a reply to message #362009] Sun, 30 November 2008 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is the chainlength if a courseid has several prerequisite chains (of different lengths)?

Regards
Michel
Re: Help regarding how to find length of chain in SQL [message #362024 is a reply to message #362009] Sun, 30 November 2008 00:56 Go to previous messageGo to next message
floverboy
Messages: 4
Registered: November 2008
Junior Member
we must find length of longest prerequisite chain
by using "CONNECT BY PRIOR" i am finding only level

please give me some hints
Re: Help regarding how to find length of chain in SQL [message #362031 is a reply to message #362024] Sun, 30 November 2008 01:07 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Hint: post your query
Prerequisite: test case: create table and insert statements along with the result you want with these data.

Regards
Michel

[Updated on: Sun, 30 November 2008 01:07]

Report message to a moderator

Previous Topic: execute immediate?
Next Topic: SQL - max(count(department_id))
Goto Forum:
  


Current Time: Wed Dec 07 02:39:41 CST 2016

Total time taken to generate the page: 0.06777 seconds