On 10/26/07, Michael Glaesemann <grzm@xxxxxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
