Adjacency list model for site hierarchy, how to retrieve all pages in this particular order?

Question

I have to admin I'm not a SQL-guru, maybe this query is really simple but I can't manage to write it. Basically all pages are arranged using the adjacency list model (I'm tracking the deep too):

+----+---------------+-------------------+-----------+
| id | title         | level | position  | parent_id |
+----+---------------+-------+-----------+-----------+
|  1 | Home          | 0     | 0         | null      |
+----+---------------+-------+-----------+-----------+
|  2 | Resources     | 0     | 1         | null      |
+----+---------------+-------+-----------+-----------+
|  3 | About         | 0     | 2         | null      |
+----+---------------+-------+-----------+-----------+
|  4 | Documents     | 1     | 0         | 2         |
+----+---------------+-------+-----------+-----------+
|  5 | Tutorials     | 1     | 1         | 2         |
+----+---------------+-------+-----------+-----------+

Note that position is unique among the same parent.

What I'm trying to achieve is to sort by parent and position, but the parent category should be listed right before its children. This is obvious and doesn't work:

SELECT * FROM PAGE p
ORDER BY parent_id, position

And result is the first table. What I need is the following:

+----+---------------+-------------------+-----------+
| id | title         | level | position  | parent_id |
+----+---------------+-------+-----------+-----------+
|  1 | Home          | 0     | 0         | null      |
+----+---------------+-------+-----------+-----------+
|  2 | Resources     | 0     | 1         | null      | // Parent of 4, 5
+----+---------------+-------+-----------+-----------+
|  4 | Documents     | 1     | 0         | 2         | // Child of Resource
+----+---------------+-------+-----------+-----------+
|  5 | Tutorials     | 1     | 1         | 2         | // Child of Resource
+----+---------------+-------+-----------+-----------+
|  3 | About         | 0     | 2         | null      |
+----+---------------+-------+-----------+-----------+

Is this possible in MySQL, not supporting recursion?

Answer 1

You can use self-joins when you can limit the query. Or try a nested-set or celko-tree.