InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 13:14:45 GMT20http://www.sqlservercentral.com/Forums/Topic771242-338-1.aspxHow “deep” does your hierarchy go?...you have given just two levels.If you have many more nested levels, such as this for example:Shirts..........Sports....................Tennis..............................Men..............................Women.....................Golf..............................Men..............................Women...........CasualThen I think it gets a little bit more difficult.Take a look at this post and RBarryYoungs’ solution to a similar question here:[url][b]http://www.sqlservercentral.com/Forums/FindPost771406.aspx[/b][/url]regards gahSun, 16 Aug 2009 16:14:15 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic771242-338-1.aspxI think that you're trying to make this more complicated than it needs to be. I was able to match your output with a simple ORDER BY clause.[code="sql"]SELECT Parent_ID, Product_ID, Product_OrderFROM Dept_Product AS pINNER JOIN Dept AS dON p.Dept_ID = d.Dept_IDORDER BY d.Parent_ID, d.Dept_ID, p.Product_Order[/code]DrewSun, 16 Aug 2009 12:41:20 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic771242-338-1.aspx[quote][b]Bob Hovious (8/14/2009)[/b][hr]Lucky, could you show us some sample data and then the expected output? I'm not sure I understand your question from what you put in the initial post.[b]gah (8/14/2009)[/b][hr]Hi..maybe I have misunderstood your question....but you ask that "want products ordered by category-wise "from the table designs that you have provided I cannot see where " category" is defined?Could you please clarify...thanks[/quote]Thanks for the replies and apologize for not giving more information.Table Schemas=============Table : Dept (Category)Column_name --> Typedept_id --> intparent_id --> intdept_order --> intname --> nvarchar(128)Table : Dept_ProductColumn_name --> Typedept_id --> intproduct_id --> intproduct_order --> intINSERT INTO Dept VALUES(1,0,1,'Shirts')INSERT INTO Dept VALUES(2,1,2,'T-Shirts')INSERT INT Dept VALUES(3,1,3,'Sport')INSERT INT Dept VALUES(4,1,4,'Casual')INSERT INT Dept VALUES((5,0,5,'Pants')INSERT INT Dept VALUES((6,5,6,'Plain')INSERT INT Dept VALUES(7,5,7,'Wide Leg')INSERT INT Dept_Product VALUES(2,101,1)INSERT INT Dept_Product VALUES(2,102,2)INSERT INT Dept_Product VALUES(2,103,4)INSERT INT Dept_Product VALUES(2,104,5)INSERT INT Dept_Product VALUES(2,106,17)INSERT INT Dept_Product VALUES(2,108,19)INSERT INT Dept_Product VALUES(2,109,3)INSERT INT Dept_Product VALUES(2,110,6)INSERT INT Dept_Product VALUES(2,111,10)INSERT INT Dept_Product VALUES(2,112,8)INSERT INT Dept_Product VALUES(3,113,7)INSERT INT Dept_Product VALUES(3,114,9)INSERT INT Dept_Product VALUES(3,115,11)INSERT INT Dept_Product VALUES(3,116,31)INSERT INT Dept_Product VALUES(3,117,27)INSERT INT Dept_Product VALUES(3,118,30)INSERT INT Dept_Product VALUES(2,119,21)INSERT INT Dept_Product VALUES(4,120,9)INSERT INT Dept_Product VALUES(4,121,11)INSERT INT Dept_Product VALUES(4,122,3)INSERT INT Dept_Product VALUES(4,123,1)INSERT INT Dept_Product VALUES(4,124,15)INSERT INT Dept_Product VALUES(4,125,5)INSERT INT Dept_Product VALUES(4,126,2)INSERT INT Dept_Product VALUES(6,127,1)INSERT INT Dept_Product VALUES(6,128,22)INSERT INT Dept_Product VALUES(6,130,24)INSERT INT Dept_Product VALUES(6,135,9)INSERT INT Dept_Product VALUES(6,138,12)INSERT INT Dept_Product VALUES(6,139,10)INSERT INT Dept_Product VALUES(6,154,6)INSERT INT Dept_Product VALUES(7,153,4)INSERT INT Dept_Product VALUES(7,129,21)INSERT INT Dept_Product VALUES(7,131,18)INSERT INT Dept_Product VALUES(7,160,12)INSERT INT Dept_Product VALUES(7,162,51)INSERT INT Dept_Product VALUES(7,163,38)INSERT INT Dept_Product VALUES(7,165,1)The output should look likeFirst Column is the Parent Category (Dept)Second Column is the product idThird column is the ranking of the product in the sub-category and is not a requirement in the output.As T-Shirts, Sport and Casual fall under the parent Shirts the order of products at the parent level should be as follows:1 --> 101 --> 11 --> 102 --> 21 --> 109 --> 31 --> 103 --> 41 --> 104 --> 51 --> 110 --> 61 --> 112 --> 81 --> 111 --> 101 --> 106 --> 171 --> 108 --> 191 --> 113 --> 71 --> 114 --> 91 --> 115 --> 111 --> 119 --> 211 --> 117 --> 271 --> 118 --> 301 --> 116 --> 311 --> 123 --> 11 --> 126 --> 21 --> 122 --> 31 --> 125 --> 51 --> 120 --> 91 --> 121 --> 111 --> 124 --> 15As Plain and Wide Leg fall under the parent Pants the order of products at the parent level should be as follows:5 --> 127 --> 15 --> 154 --> 65 --> 135 --> 95 --> 139 --> 105 --> 138 --> 125 --> 128 --> 225 --> 130 --> 245 --> 165 --> 15 --> 153 --> 45 --> 160 --> 125 --> 131 --> 185 --> 129 --> 215 --> 163 --> 385 --> 162 --> 51I hope this time I'm clear of my requirements.ThanksSun, 16 Aug 2009 08:32:35 GMTlucky-80472http://www.sqlservercentral.com/Forums/Topic771242-338-1.aspxHi..maybe I have misunderstood your question....but you ask that "want products ordered by category-wise "from the table designs that you have provided I cannot see where " category" is defined?Could you please clarify...thanks[quote]Table Schemas=============Table : DeptColumn_name Typedept_id intparent_id intdept_order intname nvarchar(128)Table : Dept_ProductColumn_name Typedept_id intproduct_id intproduct_order int[/quote]can you post some more info please[url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]Fri, 14 Aug 2009 13:32:27 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic771242-338-1.aspxLucky, could you show us some sample data and then the expected output? I'm not sure I understand your question from what you put in the initial post.Fri, 14 Aug 2009 13:28:22 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic771242-338-1.aspxHello Peers,I'm having a requirement where in trying to get products ordered by category-wise order related records as shown belowCategory Products================1 1011 1021 1032 1042 1052 1063 1073 1083 109Table Schemas=============Table : DeptColumn_name Typedept_id intparent_id intdept_order intname nvarchar(128)Table : Dept_ProductColumn_name Typedept_id intproduct_id intproduct_order intI have used different ways by using the rank functions but nowhere i'm coming too close to the solution.I would appreciate if one of you throws some light.ThanksFri, 14 Aug 2009 13:01:32 GMTlucky-80472