# Re: Hierarchical data and sorting

Date: 30 Jun 2004 12:50:57 -0700

Yes, in a little over 200 pages :)

First you need to decide on a model to use for the hierarchy. If you use a nested set or nested interval model, you can re-arrange the order of the siblings to anything you wish. Here is the code for the nested sets model:

04.08.04. Swapping Siblings

The following solution for swapping the positions of two siblings under the same parent node is due to Mr. Vanderghast and originally appeared in a posting on the MS-SQL Server Newsgroup.

If the leftmost sibling has its (lft, rgt) = (i0, i1) and the other subtree, the rightmost sibling, has (i2, i3), implicitly, we know that (i0 < i1 < i2 < i3).

With a little algebra, we can figure out that if (I) is a lft or rgt value in the table between i0 and i3, then

1. If (i BETWEEN i0 AND i1) then (i) should be updated to (i + i3- i1).
2. If (i BETWEEN i2 AND i3 then (i) should be updated to (i + i0 - i2).
3. If (i BETWEEN i1+1 AND i2-1) then (i) should be updated to (i0 + i3 + i - i2 -i1).

All of this becomes a single update statement, but we will put the (lft, rgt) pairs of the two siblings into local variables so a human being can read the code.

CREATE PROCEDURE SwapSiblings

(IN lft_sibling CHAR(2), IN rgt_sibling CHAR(2)) LANGUAGE SQL
DETERMINISTIC BEGIN ATOMIC

```DECLARE i0 INTEGER;
DECLARE i1 INTEGER;
DECLARE i2 INTEGER;
DECLARE i3 INTEGER;
SET i0 = (SELECT lft FROM Tree WHERE node = lft_sibling);
SET i1 = (SELECT rgt FROM Tree WHERE node = lft_sibling);
```
SET i2 = (SELECT lft FROM Tree WHERE node = rgt_sibling); SET i3 = (SELECT rgt FROM Tree WHERE node = rgt_sibling);

UPDATE Tree

SET lft = CASE WHEN lft BETWEEN i0 AND i1

```                  THEN i3 + lft - i1
WHEN lft BETWEEN i2 AND i3
THEN i0 + lft - i2
ELSE i0 + i3 + lft - i1 - i2 END,
rgt = CASE WHEN rgt BETWEEN i0 AND i1
THEN i3 + rgt - i1
WHEN rgt BETWEEN i2 AND i3
THEN i0 + rgt - i2
ELSE i0 + i3 + rgt - i1 - i2 END
```
WHERE lft BETWEEN i0 AND i3
```   AND i0 < i1
AND i1 < i2
AND i2 < i3;
```

END; This gives you the swap function for a sort procedure of your choice.

Now, go buy the book :) It's full of good stuff like this! Received on Wed Jun 30 2004 - 21:50:57 CEST

Original text of this message