Sorting of Subtree in SQL

[kavin]

Hi,

I am facing a problem in sorting subtree in SQL, I have a table containing the following fields:

PartnerID
Partner_ParentID
PartnerName
left
right
Depth

I want to sort only the subtree on the basis of "PartnerName" and rest tree on the basis of "left".
For Example

( assume it is sorted on the basis of "left" field.)

Mac
-->Peeter
-->Harry
-->Tison
---->Methew
---->Andrew
------->Hudson
------->Ira
---->David
-->Alisa

It should be sort on following order if i want to sort particular subtree of "Tison":

Mac
-->Peeter (sorted on ID)
-->Harry (sorted on ID)
-->Tison (sorted on ID)
---->Andrew (sorted on name)
---->David (sorted on name)
---->Methew (sorted on name)
------->Ira (sorted on name)
------->Hudson (sorted on name)
-->Alisa (sorted on ID)

I have done this using while Loop and table, is it possible to do it using one single query (as the method to sort is recursive)?

Thanx in advance.
Kavin

 

[vongrunt]

Clarification: what are left/right columns?

 

[kavin]

In Trees , the nodes are always arrange in left and right manner. the same is maintained here in database table. these fields represent the same left & rigtht.

 

[vongrunt]

If this is some kind of nested-set model, then "left" dictates sort for entire tree.

Idea: sort siblings depending on depth. This requires one self-join, though:

select A.PartnerID, A.PartnerName
from sometable A 
left join sometable B on A.Partner_ParentID = B.PartnerID
order by
case when A.depth <= 2 then A.[left] else B.[left] end,
case when A.depth <= 2 then '' else A.partnerName end

 

[kavin]

thanx for the query..

but this only upto 2nd level.. how we can solve if we need to deep levels i.e. Level 8 (lets say) also we want to maintain the Level 1 with order by "ID" and rest levels with "PartnerName

 

[kavin]

in continuation:

The above query will show the records as follows: (if say I pass "Ira")

Mac
-->Peeter (sorted on ID)
-->Harry (sorted on ID)
-->Tison (sorted on ID)
---->Andrew (sorted on name)
---->David (sorted on name)
------->Hudson (sorted on name)
------->Ira (sorted on name)
---->Methew (sorted on name)
-->Alisa (sorted on ID)

however here Ira and Hudson are under Methew.

 

[vongrunt]

Strange... can you post data (PartnerID, parent, name, left, depth) for all 10 rows?

 

[kavin]

create table #temp1(depth int ,lft int,rgt int,partnerId int, PartnerName varchar(500),partnerID_parent int )

insert into #temp1 values(1,1,2282,7765,'VIA One Technologies (51)',7765)
insert into #temp1 values(2,2,3,460,'Telecolumbia',7765)
insert into #temp1 values(2,4,1029,5220,'Mobile123, LLC (512)',7765)
insert into #temp1 values(2,1030,1107,5294,'US Telemanagement (38)',7765)
insert into #temp1 values(2,1108,1137,5305,'PV & Co. Inc. (14)',7765)
insert into #temp1 values(2,1138,1151,5671,'Edward Jaquez (6)',7765)
insert into #temp1 values(2,1152,1313,8031,'Vincent Huang & Associates, Inc. (80)',7765)
insert into #temp1 values(2,1314,1407,8087,'BTB Soft Inc (46)',7765)
insert into #temp1 values(2,1408,1425,8155,'Onnet Prepaid Communications Inc (8)',7765)
insert into #temp1 values(2,1426,1427,8249,'DST Networks',7765)
insert into #temp1 values(2,1428,1429,8268,'Suarez Travel Inc',7765)
insert into #temp1 values(2,1430,1431,8272,'E-Connect',7765)
insert into #temp1 values(2,1432,1433,8277,'Universal Data Consultants, Inc.',7765)
insert into #temp1 values(2,1434,1481,8345,'Cell Tele Communications, LLC (22)',7765)
insert into #temp1 values(2,1482,1485,8380,'Omni Phones (1)',7765)
insert into #temp1 values(2,1486,1487,8387,'ViaOne Demo',7765)
insert into #temp1 values(2,1488,1489,8418,'Omni Phones',7765)
insert into #temp1 values(2,1490,1545,8419,'Tomas Ovalle (27)',7765)
insert into #temp1 values(2,1546,1547,8459,'Dezco Communications Inc',7765)
insert into #temp1 values(2,1548,1549,8509,'World Prepaid Distributor Inc',7765)
insert into #temp1 values(2,1550,1551,8510,'2 Prepaidcard Inc',7765)
insert into #temp1 values(2,1552,1553,8524,'Fortuna Global Services Inc',7765)
insert into #temp1 values(2,1554,1749,8529,'Business Payment Systems (97)',7765)
insert into #temp1 values(2,1750,1751,8560,'Automax of CNY Inc',7765)
insert into #temp1 values(2,1752,1755,8578,'Steve Farley (1)',7765)
insert into #temp1 values(2,1756,1759,8650,'Prepaid Network Corp (1)',7765)
insert into #temp1 values(2,1760,1901,8707,'Imagine POS, Inc. (21)',7765)
insert into #temp1 values(2,1902,1903,8715,'Test Agent',7765)
insert into #temp1 values(2,1904,1909,8730,'North American Broadband and Cellular LLC (2)',7765)
insert into #temp1 values(2,1910,2015,8736,'Corporate Page.com, Inc (46)',7765)
insert into #temp1 values(2,2016,2019,8750,'DSN Enterprises (1)',7765)
insert into #temp1 values(2,2020,2183,8756,'Posa Systems LLC (30)',7765)
insert into #temp1 values(3,2021,2038,8801,'Bay Point Business Products (8)',8756)
insert into #temp1 values(3,2039,2046,8802,'Dwayne Generaux (3)',8756)
insert into #temp1 values(3,2047,2094,8803,'Steve Johnson (20)',8756)
insert into #temp1 values(3,2095,2120,8804,'Andre Morton (8)',8756)
insert into #temp1 values(4,2096,2097,8851,'Nick Kasgorgis',8804)
insert into #temp1 values(4,2098,2107,8867,'A-I Communications (4)',8804)
insert into #temp1 values(5,2099,2100,8938,'Reymac Insurance',8867)
insert into #temp1 values(5,2101,2102,8939,'Taylar Wireless',8867)
insert into #temp1 values(5,2103,2104,8940,'Taylar Wireless',8867)
insert into #temp1 values(5,2105,2106,9102,'Air One Wireless',8867)
insert into #temp1 values(4,2108,2109,8902,'Complete Digital Wireless, Inc.',8804)
insert into #temp1 values(4,2110,2111,8903,'Wirelink',8804)
insert into #temp1 values(4,2112,2113,8904,'Mikes Pagetel',8804)
insert into #temp1 values(4,2114,2115,8905,'All American',8804)
insert into #temp1 values(4,2116,2117,8941,'Haya Inc..',8804)
insert into #temp1 values(4,2118,2119,8946,'Direct Wireless',8804)
insert into #temp1 values(3,2121,2122,8807,'Freedom Wireless LLC',8756)
insert into #temp1 values(3,2123,2124,8811,'Dinny Bullard',8756)
insert into #temp1 values(3,2125,2126,8813,'Greg Yerden',8756)
insert into #temp1 values(3,2127,2128,8832,'Jim Headley',8756)
insert into #temp1 values(3,2129,2130,8837,'Marty Weitzman & Ed Potash',8756)
insert into #temp1 values(3,2131,2132,8840,'Robert Gould',8756)
insert into #temp1 values(3,2133,2134,8843,'Global Communications',8756)
insert into #temp1 values(3,2135,2136,8844,'Noorali Aliassani',8756)
insert into #temp1 values(3,2137,2142,8845,'Quick & Easy (2)',8756)
insert into #temp1 values(3,2143,2144,8846,'James Cross',8756)
insert into #temp1 values(3,2145,2146,8847,'Art Patto',8756)
insert into #temp1 values(3,2147,2148,8849,'Jay Jennings',8756)
insert into #temp1 values(3,2149,2150,8857,'Robbin Lorenz',8756)
insert into #temp1 values(3,2151,2154,8858,'Utopia PCS LLC (1)',8756)
insert into #temp1 values(3,2155,2160,8862,'Wireless Exchange (2)',8756)
insert into #temp1 values(3,2161,2162,8865,'Bob Baglini',8756)
insert into #temp1 values(3,2163,2164,8866,'POSA DEMO',8756)
insert into #temp1 values(3,2165,2166,8871,'Sandy Wolf',8756)
insert into #temp1 values(3,2167,2168,8877,'Cuellars Multiservices',8756)
insert into #temp1 values(3,2169,2170,8878,'Cell Streem',8756)
insert into #temp1 values(3,2171,2172,8883,'Bob Shook',8756)
insert into #temp1 values(3,2173,2174,8930,'FBN Wireless',8756)
insert into #temp1 values(3,2175,2176,9007,'Wireless Plus',8756)
insert into #temp1 values(3,2177,2178,9027,'Corbin Telecom',8756)
insert into #temp1 values(3,2179,2180,9031,'McNeil Communications Inc.',8756)
insert into #temp1 values(3,2181,2182,9058,'Knight Communications, Inc.',8756)
insert into #temp1 values(2,2184,2209,8776,'G3 Solutions (7)',7765)
insert into #temp1 values(2,2210,2215,8838,'Vend A Call Inc. (2)',7765)
insert into #temp1 values(2,2216,2217,8868,'Smart Debit Systems',7765)
insert into #temp1 values(2,2218,2233,8873,'Freedom Voice Inc. (6)',7765)
insert into #temp1 values(2,2234,2235,8950,'Connect Now! TeleComm LLC',7765)
insert into #temp1 values(2,2236,2239,8966,'HIT Enterprises (1)',7765)
insert into #temp1 values(2,2240,2241,8967,'HIT Interprise',7765)
insert into #temp1 values(2,2242,2243,8977,'Qpay Inc',7765)
insert into #temp1 values(2,2244,2245,8993,'PhoneShark.com Inc.',7765)
insert into #temp1 values(2,2246,2247,8994,'Q Comm Inc.',7765)
insert into #temp1 values(2,2248,2249,8995,'NUMONEY/AOPC, LLC',7765)
insert into #temp1 values(2,2250,2253,9001,'Connect 13 (1)',7765)
insert into #temp1 values(2,2254,2257,9021,'DirectConnect, Inc. (1)',7765)
insert into #temp1 values(2,2258,2263,9022,'Joseph Viens (2)',7765)
insert into #temp1 values(2,2264,2273,9048,'PTS (4)',7765)
insert into #temp1 values(2,2274,2275,1222,'In Person Payments (IPP)',7765)
insert into #temp1 values(2,2276,2277,9068,'GlobeCom International',7765)
insert into #temp1 values(2,2278,2279,9072,'Global Ventures II LLC',7765)
insert into #temp1 values(2,2280,2281,8523,'NWC X-press',7765)

 

[kavin]

I want to Sort for PartnerID 8867 which is child of
8804->8756-->7765. i.e.
It should come for
8804 (Andre Morton )
-->8867 A-I Communications (4) Not same level entry