Re: Selecting tree data

Pat Maddox wrote:
> On 10/26/07, brian <brian(at)zijn-digital(dot)com> wrote:
>>
>> Pat Maddox wrote:
>>> On 10/26/07, brian <brian(at)zijn-digital(dot)com> wrote:
>>>
>>>>
>>>> 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.
>>>
>> SELECT * FROM posts ORDER BY created_a, root_id, id;
>
> That doesn't work because it just sorts by created_at, and then if two
> records are the same it goes to root_id. That's not what I want.
>

You have been a little vague on what you are doing and what you want to
achieve - I think that if you describe exactly what you want to achieve
you will find some more help - but then at that stage you may click as
to how to achieve it anyway.

I am guessing that
root_id is the id of the first post starting the discussion
parent_id is the the post that is being responded to

root_id would be NOT NULL and would be the same as id for the first post.
I would say that parent_id SHOULD also be NOT NULL and be the same as
the id for the parent post. The lack of NULL's here would help your
sorting to go the way you want.

My guess is you want
SELECT * FROM posts ORDER BY root_id, parent_id, created_at, id;

which you would reverse as
SELECT * FROM posts ORDER BY root_id desc, parent_id desc, created_at
desc, id desc;

By the way - timestamp is accurate to 1 microsecond (I am guessing that
would be the creation timestamp) and if you manage to have two identical
timestamps the id column would tell the exact order of creation if you
wanted to be that picky. You could actually not use the created_at in
the sorting as the id will give the same effect by itself.

--

Shane Ambler
pgSQL(at)Sheeky(dot)Biz

Get Sheeky @ http://Sheeky.Biz