Re: Selecting tree data

Pat Maddox wrote:
> Right now my table looks like this:
>
> posts
> id
> body
> parent_id
> root_id
> created_at
>
> so if I've got the records
>
> (1, 'post 1', NULL, 1, '4pm')
> (2, 'post 2', NULL, 2, '8pm')
> (3, 'post 3', 1, 1, '6pm')
> (4, 'post 4', 1, 1, '5pm')
> (5, 'post 5', 4, 1, '6pm')
> (6, 'post 6', NULL, 1, '5pm')
>
> I'd like to do a select and get them all in this order:
>
> (1, 'post 1', NULL, 1, '4pm')
> (4, 'post 4', 1, 1, '5pm')
> (5, 'post 5', 4, 1, '6pm')
> (3, 'post 3', 1, 1, '6pm')
> (6, 'post 6', NULL, 1, '5pm')
> (2, 'post 2', NULL, 2, '8pm')
>
> And reverse sorted would be:
>
> (2, 'post 2', NULL, 2, '8pm')
> (6, 'post 6', NULL, 1, '5pm')
> (1, 'post 1', NULL, 1, '4pm')
> (3, 'post 3', 1, 1, '6pm')
> (4, 'post 4', 1, 1, '5pm')
> (5, 'post 5', 4, 1, '6pm')
>

SELECT * FROM posts ORDER BY root_id, id;

brian