# Re: Hierarchical data and sorting

Date: 30 Jun 2004 12:51:19 -0700

Message-ID: <18c7b3c2.0406301151.6b99166e_at_posting.google.com>

>> Does your book answer my question? <<

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

- If (i BETWEEN i0 AND i1) then (i) should be updated to (i + i3- i1).
- If (i BETWEEN i2 AND i3 then (i) should be updated to (i + i0 - i2).
- 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 ENDWHERE 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:51:19 CEST