Re: Question regarding GROUP BY - Mailing list pgsql-novice

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