| Author |
 Topic  |
|
|
dwooderson
Starting Member
USA
7 Posts |
 Posted - 09/28/2012 : 15:55:23
|
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
48076 Posts |
Posted - 09/28/2012 : 15:56:58
|
how didnt MemberB come fater A? shouldnt it be MemberC? MemberB's prev sibling is not 9
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
dwooderson
Starting Member
USA
7 Posts |
Posted - 09/28/2012 : 16:01:18
|
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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 09/28/2012 : 16:01:29
|
;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/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 09/28/2012 : 16:02:04
|
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/
|
|
|
|
dwooderson
Starting Member
USA
7 Posts |
Posted - 09/28/2012 : 16:03:20
|
| 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. . |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 09/28/2012 : 16:06:39
|
Anyways try my last query and see if it fits your need
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
dwooderson
Starting Member
USA
7 Posts |
Posted - 09/28/2012 : 16:13:25
|
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! |
|
|
|
dwooderson
Starting Member
USA
7 Posts |
Posted - 09/28/2012 : 16:17:38
|
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! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 09/28/2012 : 16:18:59
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Sorting Hierarchy Rows (self-join?)
