Hierachy Sort (with a twist)
-
I have a table output that need to be sorted on certain rules. Here is the sample output (unsorted).
Node
Level
LastChildID
LastChildDescription
TableSort
7264;18148;16545
2
16545
0.060 Sht
3
7264;18148;9690
2
9690
95W502 C M
7
7264;20433
1
20433
95W503 B F
1
7264;20433;40264
2
40264
95W504 A D
1
7264;20433;17886
2
17886
95W532 N/C
2
7264;20433;17886;18252
3
18252
0.750 Rnd
3
7264;20433;17886;42474
3
42474
95W532 -MF
7
7264;20433;40264;19354
3
19354
0.035 Weld
3
7264;20433;40264;23320
3
23320
Drill 0.25
8
7264;20433;40264;40259
3
40259
95W504 -1
2
7264;20433;40264;40263
3
40263
95W505 A V
2
7264;20433;40264;42584
3
42584
95W504 -MF
7
7264;20433;40264;46030
3
46030
95W504 Fix
8
7264;20433;40264;46671
3
46671
95W504/5051
8
7264;20433;40264;6937
3
6937
10-32 St S
4
7264;20433;40264;9693
3
9693
95W504/5052
8
7264;18147
1
18147
95W501 A M
2
7264;18147;16545
2
16545
0.060 Sht
3
7264;18147;33627
2
33627
95W501 A M
7
7264;18148
1
18148
95W502 C R
2
Each element in the Node is separated by a ';'. In this example 7264 is the parent and its children are 18147, 18148, 20433. The sorting needs to be by TableSort within the same level. If there are 2 rows with the same TableSort then it is sorted by LastChildDescription. Expected result should be
Node
Level
LastChildID
LastChildDescription
TableSort
7264;20433
1
20433
95W503 B F
1
7264;20433;17886
2
17886
95W532 N/C
2
7264;20433;17886;18252
3
18252
0.750 Rnd
3
7264;20433;17886;42474
3
42474
95W532 -MF
7
7264;20433;40264
2
40264
95W504 A D
1
7264;20433;40264;40259
3
40259
95W504 -1
2
7264;20433;40264;40263
3
40263
95W505 A V
2
7264;20433;40264;19354
3
19354
0.035 Weld
3
7264;20433;40264;6937
3
6937
10-32 St S
4
7264;20433;40264;42584
3
42584
95W504 -MF
7
7264;20433;40264;46671
3
46671
95W504/5051
8
7264;20433;40264;9693
3
9693
95W504/5052
8
7264;20433;40264;46030
3
46030
95W504 Fix
8
7264;20433;40264;23320
3
23320
Drill 0.25
8
7264;18147
1
18147
95W501 A M
2
7264;18147;16545
2
16545
0.060 Sht
3
7264;18147;33627
2
33627
95W501 A M
7
7264;18148
1
18148
95W502 C R
2
7264;18148;16545
2
16545
0.060 Sht
3
7264;18148;9690
2
9690
95W502 C M
7
Can this be done is a single query. Thanks.
-
Maybe I'm missing something, but why is 7264;20433;17886 (Level 2, TableSort 2) above 7264;20433;40264 (Level 2, TableSort 1)?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Oops. Here is the revised desired output.
Node
Level
LastChildID
LastChildDescription
TableSort
7264;20433
1
20433
95W503 B F
1
7264;20433;40264
2
40264
95W504 A D
1
7264;20433;40264;40259
3
40259
95W504 -1
2
7264;20433;40264;40263
3
40263
95W505 A V
2
7264;20433;40264;19354
3
19354
0.035 Weld
3
7264;20433;40264;6937
3
6937
10-32 St S
4
7264;20433;40264;42584
3
42584
95W504 -MF
7
7264;20433;40264;46671
3
46671
95W504/5051
8
7264;20433;40264;9693
3
9693
95W504/5052
8
7264;20433;40264;46030
3
46030
95W504 Fix
8
7264;20433;40264;23320
3
23320
Drill 0.25
8
7264;20433;17886
2
17886
95W532 N/C
2
7264;20433;17886;18252
3
18252
0.750 Rnd
3
7264;20433;17886;42474
3
42474
95W532 -MF
7
7264;18147
1
18147
95W501 A M
2
7264;18147;16545
2
16545
0.060 Sht
3
7264;18147;33627
2
33627
95W501 A M
7
7264;18148
1
18148
95W502 C R
2
7264;18148;16545
2
16545
0.060 Sht
3
7264;18148;9690
2
9690
95W502 C M
7
-
You know, people invented data normalization not only to deliver some exercises to their bored brains.
Sometimes it's really useful.
_____________
Code for TallyGenerator -
Besides of the above, the rules that were posted are pretty incomplete. Can you clarify a bit more the rules?
ORDER BY --> ?, TableSort , Level, LastChildDescription
* Noel -
The first "sort" is done on Node. The first row is a Level 1. From the sample data, 20433 is a child of 7264 and 40264 is a child of 20433.
If there are children at the same level (row# 3 to 11), then sort by TableSort column.
If the TableSort values are the same (row# 3 & 4) then sort by LastChildDescription.
-
Ignoring the fact that you probably ought to think some more about your data structure
, this query below does the trick (I think) 
If you have 'unlimited depth', then you've got even more issues to work through, however
--This SQL Script is safe to run
DECLARE @t TABLE (Node VARCHAR(50), Level INT, LastChildID INT, LastChildDescription VARCHAR(20), TableSort INT)
INSERT INTO @t (Node, Level, LastChildID, LastChildDescription, TableSort)
SELECT '7264;18148;16545', 2, 16545, '0.060 Sht ', 3
UNION ALL SELECT '7264;18148;9690', 2, 9690, '95W502 C M', 7
UNION ALL SELECT '7264;20433', 1, 20433, '95W503 B F', 1
UNION ALL SELECT '7264;20433;40264', 2, 40264, '95W504 A D', 1
UNION ALL SELECT '7264;20433;17886', 2, 17886, '95W532 N/C', 2
UNION ALL SELECT '7264;20433;17886;18252', 3, 18252, '0.750 Rnd ', 3
UNION ALL SELECT '7264;20433;17886;42474', 3, 42474, '95W532 -MF', 7
UNION ALL SELECT '7264;20433;40264;19354', 3, 19354, '0.035 Weld', 3
UNION ALL SELECT '7264;20433;40264;23320', 3, 23320, 'Drill 0.25', 8
UNION ALL SELECT '7264;20433;40264;40259', 3, 40259, '95W504 -1 ', 2
UNION ALL SELECT '7264;20433;40264;40263', 3, 40263, '95W505 A V', 2
UNION ALL SELECT '7264;20433;40264;42584', 3, 42584, '95W504 -MF', 7
UNION ALL SELECT '7264;20433;40264;46030', 3, 46030, '95W504 Fix', 8
UNION ALL SELECT '7264;20433;40264;46671', 3, 46671, '95W504/5051', 8
UNION ALL SELECT '7264;20433;40264;6937', 3, 6937, '10-32 St S', 4
UNION ALL SELECT '7264;20433;40264;9693', 3, 9693, '95W504/5052', 8
UNION ALL SELECT '7264;18147', 1, 18147, '95W501 A M', 2
UNION ALL SELECT '7264;18147;16545', 2, 16545, '0.060 Sht ', 3
UNION ALL SELECT '7264;18147;33627', 2, 33627, '95W501 A M', 7
UNION ALL SELECT '7264;18148', 1, 18148, '95W502 C R', 2
SELECT t4.*
FROM @t t1
INNER JOIN @t t2 ON t1.Node = LEFT(t2.Node, LEN(t1.Node))
INNER JOIN @t t3 ON t2.Node = LEFT(t3.Node, LEN(t2.Node))
INNER JOIN @t t4 ON t3.Node = LEFT(t4.Node, LEN(t3.Node))
WHERE
(t1.Level = 1 AND t2.Level = 1 AND t3.Level = 1 AND t4.Level = 1) OR
(t1.Level = 1 AND t2.Level = 2 AND t3.Level = 2 AND t4.Level = 2) OR
(t1.Level = 1 AND t2.Level = 2 AND t3.Level = 3 AND t4.Level = 3) OR
(t1.Level = 1 AND t2.Level = 2 AND t3.Level = 3 AND t4.Level = 4)
ORDER BY t1.Level, t1.TableSort, t1.LastChildDescription,
t2.Level, t2.TableSort, t2.LastChildDescription,
t3.Level, t3.TableSort, t3.LastChildDescription,
t4.Level, t4.TableSort, t4.LastChildDescription
Clearly this will be extremely efficient (that's irony for those who aren't sure).
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Wow. That query really rocks. I thought I would need to call a function in the Order By clause. Thanks a bunch.
Currently the table goes to 8 levels down. I will not know how many levels there would be until the top-level parent is selected (7264). Will I need to dynamically frame the sql statement based on the number of levels ?
-
Thanks Junkie
I don't think you'll need to do anything dynamically - if 8 levels is your max, just extend it as below, and that should cope with all levels (8 or less)...
SELECT t8.*
FROM @t t1
INNER JOIN @t t2 ON t1.Node = LEFT(t2.Node, LEN(t1.Node))
INNER JOIN @t t3 ON t2.Node = LEFT(t3.Node, LEN(t2.Node))
INNER JOIN @t t4 ON t3.Node = LEFT(t4.Node, LEN(t3.Node))
INNER JOIN @t t5 ON t4.Node = LEFT(t5.Node, LEN(t4.Node))
INNER JOIN @t t6 ON t5.Node = LEFT(t6.Node, LEN(t5.Node))
INNER JOIN @t t7 ON t6.Node = LEFT(t7.Node, LEN(t6.Node))
INNER JOIN @t t8 ON t7.Node = LEFT(t8.Node, LEN(t7.Node))
WHERE
(t1.Level = 1 AND t2.Level = 1 AND t3.Level = 1 AND t4.Level = 1 AND t5.Level = 1 AND t6.Level = 1 AND t7.Level = 1 AND t8.Level = 1) OR
(t1.Level = 1 AND t2.Level = 2 AND t3.Level = 2 AND t4.Level = 2 AND t5.Level = 2 AND t6.Level = 2 AND t7.Level = 2 AND t8.Level = 2) OR
(t1.Level = 1 AND t2.Level = 2 AND t3.Level = 3 AND t4.Level = 3 AND t5.Level = 3 AND t6.Level = 3 AND t7.Level = 3 AND t8.Level = 3) OR
(t1.Level = 1 AND t2.Level = 2 AND t3.Level = 3 AND t4.Level = 4 AND t5.Level = 4 AND t6.Level = 4 AND t7.Level = 4 AND t8.Level = 4) OR
(t1.Level = 1 AND t2.Level = 2 AND t3.Level = 3 AND t4.Level = 4 AND t5.Level = 5 AND t6.Level = 5 AND t7.Level = 5 AND t8.Level = 5) OR
(t1.Level = 1 AND t2.Level = 2 AND t3.Level = 3 AND t4.Level = 4 AND t5.Level = 5 AND t6.Level = 6 AND t7.Level = 6 AND t8.Level = 6) OR
(t1.Level = 1 AND t2.Level = 2 AND t3.Level = 3 AND t4.Level = 4 AND t5.Level = 5 AND t6.Level = 6 AND t7.Level = 7 AND t8.Level = 7) OR
(t1.Level = 1 AND t2.Level = 2 AND t3.Level = 3 AND t4.Level = 4 AND t5.Level = 5 AND t6.Level = 6 AND t7.Level = 7 AND t8.Level = 8)
ORDER BY t1.Level, t1.TableSort, t1.LastChildDescription,
t2.Level, t2.TableSort, t2.LastChildDescription,
t3.Level, t3.TableSort, t3.LastChildDescription,
t4.Level, t4.TableSort, t4.LastChildDescription,
t5.Level, t5.TableSort, t5.LastChildDescription,
t6.Level, t6.TableSort, t6.LastChildDescription,
t7.Level, t7.TableSort, t7.LastChildDescription,
t8.Level, t8.TableSort, t8.LastChildDescription
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
You mean "Joe Celko's Trees and Hierarchies in SQL for Smarties"?
Shame on you Joe!
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply