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.on@gmx.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
>>
>>
>
>