Re: Question regarding GROUP BY

Andreas,
I apologize, I misread your original question.

Maybe someone else could come up with something simpler, but I think
this query does what you want:
SELECT op_ts.object_id, op.product_fk, op_ts.access_ts
FROM
(SELECT object.object_id, MAX (obj_2_proj.access_ts) AS access_ts
FROM object
LEFT JOIN obj_2_proj ON object.object_id = obj_2_proj.object_fk
GROUP BY object.object_id
) AS op_ts,
obj_2_proj op
WHERE op_ts.object_id = op.object_fk AND
(op_ts.access_ts = op.access_ts OR
(op_ts.access_ts IS NULL AND
op.access_ts IS NULL AND
op.product_fk = (SELECT min(product_fk) FROM obj_2_proj WHERE
object_fk = op_ts.object_id )));

Good luck.
-Mike

On Feb 11, 2008 8:59 PM, Andreas <maps(dot)on(at)gmx(dot)net> wrote:
> 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
> >>
> >>
> >
> >
>
>