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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-15 : 08:46:07
|
| Juha writes "There's already been a question about recursive queries, but we have a problem that goes one step further:This is a simplified version of our problem:table 1: item (itemid, itemname, orgid)table 2: org (orgid, parentorg, orgname)Each item has an organisation linked to it. There are 3 levels of organisations, all linked to their parent - except top-level organisations.What we want is that the result of a query into the item and org tables should be sorted in the following order:1. top-level organisation 12. a child of 1 (1.1)3. the child(s) of 1.14. a child of 1 (1.2)5. the child(s) of 1.24. top-level organisation 2- and so on.How can this be achieved?Yours,Juha" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-15 : 09:09:12
|
Take a look at this thread:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=2828If you had a table similar to this:OrgID ParentOrg OrgName1 0 Topmost Org2 1 First Child - Billing3 1 Second Child - Marketing4 2 First Grandchild - Billing5 2 Second Grandchild - Billing6 3 First Grandchild - Marketing If you use the Lineage column, it essentially stores the entire hierarchy for each row, like this (I'm using the | character to delimit instead of the backslash):OrgID Lineage1 |2 |1|3 |1|4 |1|2|5 |1|2|6 |1|2|3| If you then ORDER BY the Lineage column, you'll get the proper nesting. The following query should work:SELECT * FROM Org ORDER BY Lineage + Ltrim(Str(OrgID,8,0))You'd need to add the OrgID to the Lineage string in order for the children to nestle underneath the parents.Edited by - robvolk on 04/15/2002 09:12:59 |
 |
|
|
|
|
|
|
|
Sorting recursive queries