>I am trying to create a SQL query to select records from a table and order them the way I want the ordered. I am sure it is possible but I just can't figure it out right now.
>Imagine the following table:
>

>nMsgId  nParentId
> 1       1
> 8       1
>10       1
>11      10
>12       8
>13      12
>

>
>Now i want to run a select statement:
>

>select nMsgId,nParentId from myTable order by ?????
>

>
>And I would like the following result
>

>nMsgId  nParentId
> 1       1
> 8       1
>12       8
>13      12
>10       1
>11      10
>

>
>Is this possible to do?
>It will be used for a messagethreading system, kinda like UT messages are organized (don't worry I am not thinking about competing with UT <s>).
>
>Should I stop thinking about running a select statement and run a recursive look type thing instead?

How about this:

>

>nMsgId  nParentId  nThreadID
> 1       1              1
> 8       1              1
>12       8              1
>13      12              1
>10       4              4
>11      10              4
>

And order by nthreadId, nParentId, nMsgID. But that won't do you much good if they all belong to the same thread.

One thing I do for treelike stuff is to build a composite key as I go, one that would give me the exact tree ordering if ordered by. It's the parent's key (abbreviated using bintoc() or a similar function) plus the current record's key. However, this only goes so far - there's a few bytes added at each level, and if I take bintoc(), it goes to no more than 240/4=60 levels. Usually, I don't need to go deeper than 4-5, because I use this for spacial or logical containership (as in "is located in" or "belongs to", restaurant recipes, assembly lists, organizational structures), and they say even an ocean liner doesn't disassemble in more than ten levels.

However, for a message thread, if you intend to become as popular as this place... you better go recursive and build these keys on your selected data, not on your underlying table, or don't build any keys, just copy records into an empty cursor one by one, going recursively over your original data.

Or go recursively and assign them ordinal numbers as you go, then index by this ordinal number. That would be the simplest.