Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group