Re: Selecting tree data

On 10/26/07, brian <brian(at)zijn-digital(dot)com> wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>

Okay, but what if I want to order by created_at?

btw created_at is a timestamp, I just wrote '4pm' to make it a bit
easier to read.

Pat