Question regarding GROUP BY

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: ListaPostgre <pgsql-novice(at)postgresql(dot)org>
Subject: Question regarding GROUP BY
Date: 2008-02-11 03:43:08
Message-ID: 47AFC44C.8030701@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Brian Hurt 2008-02-11 19:10:38 Memory-leak-like effect on insane query (postgres 8.1.6)
Previous Message Charley Tiggs 2008-02-09 04:22:30 Re: sync two databases