June 9, 2008 at 1:00 pm
Hello guys,
I have a table with the following structure:
ID
ParentID
Node
with data like:
1 0 aaaa
2 0 bbbb
3 1 aaaa-aaa
4 2 bbbb-aaa
5 3 aaaa-aaa-aaa
Please notice every node has its own ID as well as a parent node
Now I want my select result be sorted as its hierarchy:
aaaa
aaaa-aaa
aaaa-aaa-aaa
bbbb
bbbb-aaa
....
Can anyone tell me how do I do that?
Thanks in advance.
June 9, 2008 at 1:10 pm
"Order BY Node" looks like it would work on your data.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2008 at 1:41 pm
Sorry I didn't describe it clearer:
In my table, every node has its sub tree, so what I would need is to iterate node by node, simply order by any field wouldn't work. Let me give you another example below. As you can see, the hierarchy is very clear, what I want the final query be is:
1
11
44
45
12
13
46
47
48
49
50
51
52
81
14
15
16
53
54
55
56
17
18
...
Sample data:
ID ParentID
10
20
30
40
50
60
70
80
90
100
111
121
131
141
151
161
171
181
192
202
212
222
233
243
253
263
275
285
295
306
316
326
337
348
358
369
379
389
399
409
4110
4210
4411
4511
4613
4713
4813
4913
5013
5113
5213
8113
5316
5416
5516
5616
5723
5823
5923
6023
6123
6223
6324
6424
6524
6624
6725
6825
6925
7025
7126
7226
7326
7426
7526
7626
7733
7833
7933
8038
8238
8338
8438
8538
8638
9638
9738
9938
8739
8839
8939
9039
9139
9240
9340
9440
9540
9840
10040
10140
10240
10340
10440
10540
10641
10741
10841
10941
11042
11142
11277
11377
11477
11582
11682
11782
11882
11982
12082
12182
12282
12382
12482
12582
12682
12782
12882
12982
13082
13182
13283
13383
June 11, 2008 at 12:45 am
the best thing you can do is to add a field with the "path" of each node
something like this:
001.001.001.001.003
then your life will be easier. the key is to make a recursive function to fill that field
this is one im using
CREATE FUNCTION dbo.f_MNUItemTreePath_Get (@IdMNUItem VARCHAR(50), @IdSubsystem VARCHAR(50))
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @IdParent VARCHAR(50),
@PathVARCHAR(500)
SELECT @IdParent = IdParent
FROM FRMK.dbo.t_MNUItems
WHERE IdMNUItem = @IdMNUItem
AND IdSubsystem = @IdSubsystem
IF @IdParent IS NOT NULL
SELECT @Path= dbo.f_MNUItemTreePath_Get(@IdParent, @IdSubsystem )+'/'+@IdMNUItem
ELSE
SELECT @Path = @IdSubsystem+ISNULL('/'+@IdMNUItem,'')
IF @IdSubsystem IS NULL
SELECT @Path = 999
RETURN @Path
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply