Another ORDER BY - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Another ORDER BY

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-11-17 : 02:34:58

Might be an odd question but I defer to the Guru's:

I have a CTE Hierarchy working well. I pass it an ID and it return the data correctly except that the value passed is in the 'incorrect' place.

So for example I pass it 35316 and get the following hierarchy where there are basically 2 nodes at the lowest level:


2	NULL	Item A
4	2	Item B
35316	NULL	Item X

I would like to order it with passed parameter i.e 35316 first. But this parameter value could be anything i.e. not necessarily sequential compared to the results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 02:44:27

add a new field to your CTE body as OrderVal. Set 1 as its value for anchor memeber and 2 for recrsive member. then in select statement from CTE order based on OrderVal field first 7 then curent field. this should give you what you want.
If still in doubt,post you CTE definition query

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-11-17 : 02:54:29

visakh16:

What you're saying makes perfect sense. I'm just not sure how to set the OrderVal for the anchor member when multiple members may have a ParentID as NULL:


SELECT @AssemblyID = ID FROM Assemblies WHERE ObjectID = @ObjectID;

WITH CTE_Hierarchy(ObjectID, ParentID, Consumption, Depth, Sort)
	AS (
		SELECT ObjectID, ParentID, 1, CAST(ObjectID AS VARBINARY(900))	FROM AssembliesRelationships WHERE AssemblyID = @AssemblyID AND ParentID IS NULL
			UNION ALL
		SELECT ars.ObjectID, ars.ParentID, ars.Consumption, Depth + 1, CAST(Sort + CAST(ars.ObjectID AS BINARY(4)) AS VARBINARY(900))	FROM AssembliesRelationships ars INNER JOIN CTE_Hierarchy ON ars.ParentID = CTE_Hierarchy.ObjectID
	)

	SELECT Objects.ID, ISNULL(CTE_Hierarchy.ParentID, 0) AS ParentID, Objects.ProductName, Rates.Material, CTE_Hierarchy.Consumption, CTE_Hierarchy.Depth, CTE_Hierarchy.Sort FROM Objects INNER JOIN Rates ON Objects.ID = Rates.ObjectID INNER JOIN CTE_Hierarchy ON Objects.ID = CTE_Hierarchy.ObjectID ORDER BY Sort, Objects.ProductName

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-11-17 : 02:55:11

Sorry, how do I wrap long lines in posted code?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 02:58:34

something like below

SELECT @AssemblyID = ID FROM Assemblies WHERE ObjectID = @ObjectID;

WITH CTE_Hierarchy(ObjectID, ParentID, Consumption, Depth, Sort,OrderVal)
	AS (
SELECT ObjectID, ParentID, 1, 
CAST(ObjectID AS VARBINARY(900)),
1 	
FROM AssembliesRelationships 
WHERE AssemblyID = @AssemblyID 
AND ParentID IS NULL
UNION ALL
SELECT ars.ObjectID, 
ars.ParentID, 
ars.Consumption, 
Depth + 1, 
CAST(Sort + CAST(ars.ObjectID AS BINARY(4)) AS VARBINARY(900)),
2	
FROM AssembliesRelationships ars 
INNER JOIN CTE_Hierarchy ON ars.ParentID = CTE_Hierarchy.ObjectID
)

SELECT Objects.ID, 
ISNULL(CTE_Hierarchy.ParentID, 0) AS ParentID, Objects.ProductName, Rates.Material, 
CTE_Hierarchy.Consumption, 
CTE_Hierarchy.Depth, 
CTE_Hierarchy.Sort 
FROM Objects 
INNER JOIN Rates 
ON Objects.ID = Rates.ObjectID 
INNER JOIN CTE_Hierarchy 
ON Objects.ID = CTE_Hierarchy.ObjectID 
ORDER BY OrderVal,Sort, Objects.ProductName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 03:00:10

quote:
Originally posted by John Sourcer

Sorry, how do I wrap long lines in posted code?

post code between code tags

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-11-17 : 03:12:05

Thanks Visakh16 but that still has a problem because ID 2 also has a parent that is null. Maybe a conditional query? Along teh lines of:


SELECT ObjectID, ParentID, 1, CAST(ObjectID AS VARBINARY(900)), 
CASE
 WHEN ObjectID = @ObjectID
  '1'
 ELSE
  '2'	
 END


2	NULL	Item A
4	2	Item B
35316	NULL	Item X

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 03:17:11

Do you mean you've morethan 1 record with AssemblyId=@AssemblyID? can you give some sample of how your data is and how you want to sort them?

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-11-17 : 03:27:30

:) Sorry if I wasn't clear. I am passing ID 35316 to the SP and want it returned first. Here is my Assemblies table


ID	AccountID	ObjectID
1	2		35316

and my AssembliesRelationships


ID	AssemblyID	ObjectID	ParentID
1	1		35316		NULL
2	1		2		NULL
3	1		4		2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 03:36:09

SELECT @AssemblyID = ID FROM Assemblies WHERE ObjectID = @ObjectID;

WITH CTE_Hierarchy(ObjectID, ParentID, Consumption, Depth, Sort,OrderVal)
	AS (
SELECT ObjectID, ParentID, 1, 
CAST(ObjectID AS VARBINARY(900)),
CASE WHEN Object_ID=@Object_ID THEN  1 ELSE 2 END	
FROM AssembliesRelationships 
WHERE AssemblyID = @AssemblyID 
AND ParentID IS NULL
UNION ALL
SELECT ars.ObjectID, 
ars.ParentID, 
ars.Consumption, 
Depth + 1, 
CAST(Sort + CAST(ars.ObjectID AS BINARY(4)) AS VARBINARY(900)),
2	
FROM AssembliesRelationships ars 
INNER JOIN CTE_Hierarchy ON ars.ParentID = CTE_Hierarchy.ObjectID
)

SELECT Objects.ID, 
ISNULL(CTE_Hierarchy.ParentID, 0) AS ParentID, Objects.ProductName, Rates.Material, 
CTE_Hierarchy.Consumption, 
CTE_Hierarchy.Depth, 
CTE_Hierarchy.Sort 
FROM Objects 
INNER JOIN Rates 
ON Objects.ID = Rates.ObjectID 
INNER JOIN CTE_Hierarchy 
ON Objects.ID = CTE_Hierarchy.ObjectID 
ORDER BY OrderVal,Sort, Objects.ProductName

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-11-17 : 03:41:39

I bow before your great knowledge, oh mighty Yak!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 03:44:40

quote:
Originally posted by John Sourcer

I bow before your great knowledge, oh mighty Yak!

Cheers

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-11-17 : 06:58:59

Sorry Yak et al,

Is there a way to know if you're at the end of a node i.e. at a leaf node or should this be handled in code?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 07:09:59

quote:
Originally posted by John Sourcer

Sorry Yak et al,

Is there a way to know if you're at the end of a node i.e. at a leaf node or should this be handled in code?

take max of Depth value and nodes at that level will give you leaf nodes.

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-11-17 : 07:36:18

Thanks visakh16,

Surely MAX(depth) will be the same as Depth? (I'm obviously out of mine here)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 09:46:01

quote:
Originally posted by John Sourcer

Thanks visakh16,

Surely MAX(depth) will be the same as Depth? (I'm obviously out of mine here)

nope. it will be equal to maximum value of Depth.

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-11-18 : 01:35:16

Sorry Visakh16, I'm a bit lost here. I wrote a code solution but it's not as clean as I would like.

Where would I query MAX(Depth), right at the end of the procedure?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 03:38:49

This will give you leaf nodes

SELECT Objects.ID, 
ISNULL(CTE_Hierarchy.ParentID, 0) AS ParentID, Objects.ProductName, Rates.Material, 
CTE_Hierarchy.Consumption, 
CTE_Hierarchy.Depth, 
CTE_Hierarchy.Sort 
FROM Objects 
INNER JOIN Rates 
ON Objects.ID = Rates.ObjectID 
INNER JOIN CTE_Hierarchy 
ON Objects.ID = CTE_Hierarchy.ObjectID
WHERE  CTE_Hierarchy.Depth=(SELECT MAX(Depth) FROM CTE_Hierarchy)
ORDER BY OrderVal,Sort, Objects.ProductName

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2008-11-18 : 04:47:01

Correct. I'm trying to figure a way to denote nodes as leaf in all of the returned data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 06:25:40

quote:
Originally posted by John Sourcer

Correct. I'm trying to figure a way to denote nodes as leaf in all of the returned data.

just include a bit feild at end to denote leaf nodes

SELECT @AssemblyID = ID FROM Assemblies WHERE ObjectID = @ObjectID;


WITH CTE_Hierarchy(ObjectID, ParentID, Consumption, Depth, Sort,OrderVal)
	AS (
SELECT ObjectID, ParentID, 1, 
CAST(ObjectID AS VARBINARY(900)),
CASE WHEN Object_ID=@Object_ID THEN  1 ELSE 2 END	
FROM AssembliesRelationships 
WHERE AssemblyID = @AssemblyID 
AND ParentID IS NULL
UNION ALL
SELECT ars.ObjectID, 
ars.ParentID, 
ars.Consumption, 
Depth + 1, 
CAST(Sort + CAST(ars.ObjectID AS BINARY(4)) AS VARBINARY(900)),
2	
FROM AssembliesRelationships ars 
INNER JOIN CTE_Hierarchy ON ars.ParentID = CTE_Hierarchy.ObjectID
)

SELECT Objects.ID, 
ISNULL(CTE_Hierarchy.ParentID, 0) AS ParentID, Objects.ProductName, Rates.Material, 
CTE_Hierarchy.Consumption, 
CTE_Hierarchy.Depth, 
CTE_Hierarchy.Sort ,
CASE WHEN CTE_Hierarchy.Depth=MAX(CTE_Hierarchy.Depth) OVER() THEN 1 ELSE 0 END AS LeafNode
FROM Objects 
INNER JOIN Rates 
ON Objects.ID = Rates.ObjectID 
INNER JOIN CTE_Hierarchy 
ON Objects.ID = CTE_Hierarchy.ObjectID 
ORDER BY OrderVal,Sort, Objects.ProductName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources