SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get products ordered by category order


Get products ordered by category order

Author
Message
lucky-80472
lucky-80472
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1513 Visits: 842
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
================
1 101
1 102
1 103

2 104
2 105
2 106

3 107
3 108
3 109

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
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5398 Visits: 6900
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
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5460 Visits: 35408
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

lucky-80472
lucky-80472
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1513 Visits: 842
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
drew.allen
drew.allen
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6252 Visits: 10871
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 to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5460 Visits: 35408
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search