Hi all,

I've got an table like this:

TABLE:
id int(8) PRIMARY AUTO_INCREMENT
name varchar(80)
parent int(8) INDEX
order int(4)

It's a recursive table, meaning parent contains a reference to id.
Effectively, rows can have childs. `order` is a dynamic way in which order
the pages names are displayed within their parent. Parent/child nesting can
be arbitrary deep. (Yes, the adjecency model).

Now, what I want is a query that will order every record by their root
parent, than their next parent etc... I can't produce left joins forever :-)
Problem is the tree can be arbitrary deep. I begin do doubt this is even
possible in one query. Will I have to resort to a nested set model?

Not unimportant: MySQL 4, so no stored procedures...

Grtz
--
Rik Wasmus