ordering ordering question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ordering ordering question

Author

Topic

cyclechick
Starting Member

2 Posts

Posted - 2009-06-26 : 03:51:25

orders ordering query

Ive been passed a database that Im having trouble getting data out in the order I want.Ive done a quick search but Ive not been able to find anything close to what I need so Im hoping you good folks can help.
I have an orders table storing orders,the data in the following format...
id,ProductNo,LinkedProduct,Price,Qty

id is an autonumber,ProductNo is an integer in the range 1 - 100, Price and Qty are obvious enough.Products can have other products linked to them,gift wrapping for example.These products are ranged 1000 - 1100.
The data is structured so that whilst the actual item being bought doesnt have a LinkedProduct (its a null value) the gift wrapping will have its product number in the ProductNo field but the product to be wrapped in the LinkedProduct field - I hope that makes sense

What I want to do is be able to list each actual item with any associated items immediately after it.
Ordering by id works most of the time but it does introduce problems when adding gift wrapping to one product after a second product is added.
It had occurred to me to populate LinkedProduct with itself for the top level and order by that,but that leaves the problem that using order by will list them in product order rather than added to the basket order.

ie
id,ProductNo,LinkedProduct,Price,Qty
1,100,,10.5,1
2,1005,100,3,1
3,1006,100,2.5,1
4,105,,12,1
5,1005,105,1
6,1003,100,1

Order Id like to see
id,ProductNo,LinkedProduct,Price,Qty
1,100,,10.5,1
2,1005,100,3,1
3,1006,100,2.5,1
6,1003,100,1
4,105,,12,1
5,1005,105,1

Thanks in advance for any help you can provide

CC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 04:00:22

This recursive CTE will give you a hint how to accomplish this

DECLARE	@Sample TABLE
	(
		id INT,
		ProductNo INT,
		LinkedProduct INT,
		Price INT,
		Qty INT
	)

INSERT	@Sample
SELECT	1,  100, NULL, 10.5, 1 UNION ALL
SELECT	2, 1005,  100,  3,   1 UNION ALL
SELECT	3, 1006,  100,  2.5, 1 UNION ALL
SELECT	4,  105, NULL, 12,   1 UNION ALL
SELECT	5, 1005,  105,  0,   1 UNION ALL
SELECT	6, 1003,  100,  0,   1

;WITH Yak(ID, ProductNo, pth)
AS (
	SELECT	ID,
		ProductNo,
		'/' + CAST(ProductNo AS VARCHAR(MAX)) + '/'
	FROM	@Sample
	WHERE	LinkedProduct IS NULL

	UNION ALL

	SELECT		s.ID,
			s.ProductNo,
			y.pth + CAST(s.ProductNo AS VARCHAR(MAX)) + '/'
	FROM		@Sample AS s
	INNER JOIN	Yak AS y ON y.ProductNo = s.LinkedProduct
)

SELECT		*
FROM		Yak
ORDER BY	pth