How to order a Parent & Child table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to order a Parent & Child table

Author

Topic

scozzese
Starting Member

12 Posts

Posted - 2002-07-18 : 03:09:04

I have the following table:
Id ParentId
1 7
5 1
3 5
7

and I want to order it in the following way:

Id ParentId
7
1 7
5 1
3 5

What is the correct sintax in SQL?
Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-18 : 05:15:49

you can't easily......unless you go down something like the following route.....

7.0.0.0
7.1.0.0
7.1.5.0
7.1.5.3

because what you are looking for is a sequence/order based upon a row's relationship to (some) other rows (ie it's parents), and not a an order based on an existing piece of data that is common to all rows...

but by putting the above data on each row....you can sort by this "extra" data....the new problem you now face is "how deep is the hierarchy?, and the cost I will incur as I need to calculate each row's ENTIRE parentage before sorting"....in the above example i've worked to your sample data and limited it to 4 levels....

it can be done....but it's not a simple 1 line command.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-18 : 08:27:06

Take a look here:

http://www.sqlteam.com/item.asp?ItemID=8866

If you use a lineage column, you can then ORDER BY Lineage to get the order you want.

scozzese
Starting Member

12 Posts

Posted - 2002-07-23 : 06:12:19

About this problem I find this solution for Oracle:

create table tree (father varchar (20) not null,child varchar (20)not null)

Father Child
Level 1 Level 2a
Level 1 Level 2b
Level 2a Level 3a
Level 2a Level 3b
Level 2b Level 3C
Leve 3b Level 4a

select father , child
from tree
connect by prior child =father
start with father="Level 1"
with this query i got what I want.

Now the problem is to convert the query for Micorsoft Sql Server 2000.
What is the correct sintax

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-23 : 08:11:46

Did you read the article I posted earlier?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-07-23 : 08:11:58

There isn't an equivalent command in SQL
The "connect by" is unique to Oracle.

Take a look at the link in robvolk's post above.

scozzese
Starting Member

12 Posts

Posted - 2002-07-23 : 08:25:28

I read your article but I prefer to find a solution without changing the structure of the table, with a simple query or a stored procedure.
Could you help me in this way?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-26 : 09:22:59

converting from Oracle to SQL often isn't an easy as one might think....

Different products, different syntax, different non-standard features......and different PRICES!!!!!

The cost of incorporating a non-standard feature comes into play when porting a solution to another platform....

and it may not be a direct financial cost....sometimes it's a choice between altering table designs, altering the current (pre-port) solution, accepting a suggested alternative.....

..........or accepting that it can't be done as simply or as wished for!!!

your money, your choice!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources