Get products ordered by category order

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Get products ordered by category order

Get products ordered by category order

Rate Topic

Display Mode

Topic Options

Author

Message

lucky-80472

Posted Friday, August 14, 2009 1:01 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 8:51 AM
Points: 1,295, Visits: 733

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

Post #771242

The Dixie Flatline

Posted Friday, August 14, 2009 1:28 PM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:41 AM
Points: 3,788, Visits: 5,538

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? -- Stephen Stills

Post #771270

J Livingston SQL

Posted Friday, August 14, 2009 1:32 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 1,451, Visits: 14,223

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 !
__________________________________________________________________

Post #771272

lucky-80472

Posted Sunday, August 16, 2009 8:32 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 8:51 AM
Points: 1,295, Visits: 733

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)

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

Post #771618

drew.allen

Posted Sunday, August 16, 2009 12:41 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 1:20 PM
Points: 1,235, Visits: 5,389

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

Post #771646

J Livingston SQL

Posted Sunday, August 16, 2009 4:14 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 1,451, Visits: 14,223

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 !
__________________________________________________________________

Post #771656

« Prev Topic | Next Topic »

Permissions