Re: Selecting tree data

On 10/26/07, Michael Glaesemann <grzm(at)seespotcode(dot)net> wrote:
>
> On Oct 26, 2007, at 10:56 , Pat Maddox wrote:
>
> > A bunch of options so far...but there's really no way to do this with
> > standard SQL?
>
> What do you mean by "standard SQL"? Trees aren't inherently relational.

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')

Does that make sense?

Pat