SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sorting Hierarchy Rows (self-join?)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dwooderson
Starting Member

USA
7 Posts

Posted - 09/28/2012 :  15:55:23  Show Profile  Reply with Quote
I'm extracting data from some application tables and need to sort the rows in my SQL Server 10 staging database based on "PrevSiblingID" and "NextSiblingID" which basically define the the order of the list.

I'll be honest, I'm in over my head here. Any suggestions?

Sample Table and Inserts:

CREATE TABLE [dbo].[A_Hierarchy](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ItemName] [varchar](50) NULL,
[PrevSiblingID] [int] NULL,
[NextSiblingID] [int] NULL)

INSERT INTO A_Hierarchy2 values ('Member G',10,8)
INSERT INTO A_Hierarchy2 values ('Member D',5,7)
INSERT INTO A_Hierarchy2 values ('Member J',4,NULL)
INSERT INTO A_Hierarchy2 values ('Member I',8,3)
INSERT INTO A_Hierarchy2 values ('Member C',9,2)
INSERT INTO A_Hierarchy2 values ('Member A',NULL,9)
INSERT INTO A_Hierarchy2 values ('Member E',2,10)
INSERT INTO A_Hierarchy2 values ('Member H',1,4)
INSERT INTO A_Hierarchy2 values ('Member B',6,5)
INSERT INTO A_Hierarchy2 values ('Member F',7,1)

The desired result is a list of ItemNames, ordered based on the relationship to the other rows as defined in the next/previous columnids.

Desired Result:
Member A
Member B
Member C
...

(the ItemNames won't normally be sortable by alpha like they are in this example)

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/28/2012 :  15:56:58  Show Profile  Reply with Quote
how didnt MemberB come fater A? shouldnt it be MemberC? MemberB's prev sibling is not 9

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dwooderson
Starting Member

USA
7 Posts

Posted - 09/28/2012 :  16:01:18  Show Profile  Reply with Quote
I think I got that example right.. for "Member B",

PrevSiblingId: 6 (which is Member A)
NextSiblingId: 5 (which is Member C)

and so on.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/28/2012 :  16:01:29  Show Profile  Reply with Quote

;With CTE
AS
(SELECT ItemName,NextSiblingID,CAST(1  AS int)AS [Level]
FROM A_Hierarchy2
WHERE PrevSiblingID IS NULL
UNION ALL
SELECT  h.ItemName,h.NextSiblingID,c.Level+1
FROM CTe c
INNER JOIN A_Hierarchy2 h
ON h.PrevSiblingID = c.NextSiblingID
)

SELECT itemName
FROM CTE
ORDER BY [Level]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/28/2012 :  16:02:04  Show Profile  Reply with Quote
quote:
Originally posted by dwooderson

I think I got that example right.. for "Member B",

PrevSiblingId: 6 (which is Member A)
NextSiblingId: 5 (which is Member C)

and so on.


A is 9 and not 6

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dwooderson
Starting Member

USA
7 Posts

Posted - 09/28/2012 :  16:03:20  Show Profile  Reply with Quote
Maybe the confusion is that in this example, I'm relying on an identity column to assign the IDs in the order the inserts execute. Sorry, maybe I should have explicitly set that for example purposes. .
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/28/2012 :  16:06:39  Show Profile  Reply with Quote
Anyways try my last query and see if it fits your need

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dwooderson
Starting Member

USA
7 Posts

Posted - 09/28/2012 :  16:13:25  Show Profile  Reply with Quote
Result from your query (missing half of the rows)

Member A
Member C
Member E
Member G
Member I

The Next/Prev columns relate the first column, "[ID]". I've tried adapting your query, but have't gotten it yet..

BTW, thanks for your very fast replies!
Go to Top of Page

dwooderson
Starting Member

USA
7 Posts

Posted - 09/28/2012 :  16:17:38  Show Profile  Reply with Quote
I think I got it:

;With CTE
AS
(SELECT id,ItemName,NextSiblingID,CAST(1 AS int)AS [Level]
FROM A_Hierarchy2
WHERE PrevSiblingID IS NULL
UNION ALL
SELECT h.id,h.ItemName,h.NextSiblingID,c.Level+1
FROM CTe c
INNER JOIN A_Hierarchy2 h
ON h.PrevSiblingID = c.id
)

SELECT itemName
FROM CTE
ORDER BY [Level]


Thank you so much for your help, this is awesome!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/28/2012 :  16:18:59  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000