Get products ordered by category order

  • Hello Peers,

    I'm having a requirement where in trying to get products ordered by category-wise order related records as shown below

    Category Products

    ================

    1101

    1102

    1103

    2104

    2105

    2106

    3107

    3108

    3109

    Table Schemas

    =============

    Table : Dept

    Column_name Type

    dept_id int

    parent_id int

    dept_order int

    name nvarchar(128)

    Table : Dept_Product

    Column_name Type

    dept_id int

    product_id int

    product_order int

    I 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.

    Thanks


    Lucky

  • 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.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • 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

    Table Schemas

    =============

    Table : Dept

    Column_name Type

    dept_id int

    parent_id int

    dept_order int

    name nvarchar(128)

    Table : Dept_Product

    Column_name Type

    dept_id int

    product_id int

    product_order int

    can you post some more info please

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Bob Hovious (8/14/2009)


    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.

    gah (8/14/2009)


    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

    Thanks for the replies and apologize for not giving more information.

    Table Schemas

    =============

    Table : Dept (Category)

    Column_name --> Type

    dept_id --> int

    parent_id --> int

    dept_order --> int

    name --> nvarchar(128)

    Table : Dept_Product

    Column_name --> Type

    dept_id --> int

    product_id --> int

    product_order --> int

    INSERT 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 like

    First Column is the Parent Category (Dept)

    Second Column is the product id

    Third 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 --> 1

    1 --> 102 --> 2

    1 --> 109 --> 3

    1 --> 103 --> 4

    1 --> 104 --> 5

    1 --> 110 --> 6

    1 --> 112 --> 8

    1 --> 111 --> 10

    1 --> 106 --> 17

    1 --> 108 --> 19

    1 --> 113 --> 7

    1 --> 114 --> 9

    1 --> 115 --> 11

    1 --> 119 --> 21

    1 --> 117 --> 27

    1 --> 118 --> 30

    1 --> 116 --> 31

    1 --> 123 --> 1

    1 --> 126 --> 2

    1 --> 122 --> 3

    1 --> 125 --> 5

    1 --> 120 --> 9

    1 --> 121 --> 11

    1 --> 124 --> 15

    As Plain and Wide Leg fall under the parent Pants the order of products at the parent level should be as follows:

    5 --> 127 --> 1

    5 --> 154 --> 6

    5 --> 135 --> 9

    5 --> 139 --> 10

    5 --> 138 --> 12

    5 --> 128 --> 22

    5 --> 130 --> 24

    5 --> 165 --> 1

    5 --> 153 --> 4

    5 --> 160 --> 12

    5 --> 131 --> 18

    5 --> 129 --> 21

    5 --> 163 --> 38

    5 --> 162 --> 51

    I hope this time I'm clear of my requirements.

    Thanks


    Lucky

  • I 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.

    SELECT Parent_ID, Product_ID, Product_Order

    FROM Dept_Product AS p

    INNER JOIN Dept AS d

    ON p.Dept_ID = d.Dept_ID

    ORDER BY d.Parent_ID, d.Dept_ID, p.Product_Order

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • How “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

    ...........Casual

    Then I think it gets a little bit more difficult.

    Take a look at this post and RBarryYoungs’ solution to a similar question here:

    http://www.sqlservercentral.com/Forums/FindPost771406.aspx

    regards gah

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply