Re: Question regarding GROUP BY

Michael,

thanks for your comment but it doesnt help or I don't see it.

obj_2_project holds those 2 foreign keys that form the primary together
with a timestamp that can be NULL.
Like this:
1 1 2008/01/01
1 2 NULL
1 3 2008/03/03
2 1 NULL
2 2 NULL
3 1 NULL
3 3 2008/05/05

Now I need the latest timestamp grouped by the object-fk column (1).
But I also need the projekt-fk where this latest time appeared
See:

1 3 2008/03/03
2 1 NULL
3 3 2008/05/05

As for object-id 2 this might get a wee bit tricky because the latest
timestamp is NULL and appears in 2 lines.
Actually in the all-NULL-case the project-id is irrelevant so one could
be picked.

Michael Swierczek schrieb:
> Andreas,
> Maybe I'm misunderstanding you, but wouldn't it just be this?
> SELECT * FROM obj_2_proj ORDER BY access_ts DESC;
> If object_fk and project_fk are the primary key of obj_2_proj,
> each object/project combination can only appear in the table once.
>
> -Mike
>
> On Feb 10, 2008 10:43 PM, Andreas <maps(dot)on(at)gmx(dot)net> wrote:
>
>> Hi,
>>
>> I've got 3 tables:
>> objects (object_id integer primary ...)
>> projects (project_id integer primary ...)
>>
>> in the 3rt table I store an m:n relation
>> obj_2_proj (object_fk, project_fk, access_ts timestamp, primary
>> key (object_fk, project_fk))
>>
>> Now I need to know the projekt and access_ts of all those objekt_fk with
>> the highest access_ts.
>> This highest access_ts might be NULL.
>> I tried:
>>
>> SELECT object_fk, project_fk, max(access_ts)
>> FROM obj_2_proj
>> GOUP BY object_fk;
>>
>> Postgres doesnt like this and complains, I had to include project_fk in
>> the GROUP BY but if I do this I get every line out of this table since
>> (object_fk, project_fk) is the primary key.
>>
>> What to do?
>>
>>
>> Regards
>> Andreas
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>>
>
>