Sorting out SQL Trees
Saturday, October 29th, 2005I recently came upon a problem that had a surprisingly pleasant solution. I had set of elements that look like this:
| Element | Bucket |
The "Bucket" for each element was a member of a hierarchical (and editable) tree. Usually, people represent a tree like this in SQL using the "adjency list model", where the table is repeatedly joined with itself on ID=parentID to produce the tree. The schema for that table looks like this:
| Bucket | ID | parentID | Order |
My hierarchy also has order, so that each child of a given parent has a sequential order.
The problem I had was that I wanted to sort my elements in an order based on the order of the tree so that I could have a report that looked like this:
1. Bucket
Element
Element
1.1 Bucket2
Element
Element











