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

How about
SELECT   DISTINCT ON (project_fk)
    project_fk, object_fk, access_ts
 FROM    obj_2_proj
ORDER BY project_fk, access_ts DESC

If you want to include those that have no related object then do

SELECT   DISTINCT ON (p.project_id)
    p.project_id, o.object_fk,  o.access_ts
 FROM    projects As p LEFT JOIN obj_2_proj As o ON p.project_id =
o.project_fk
ORDER BY p.project_id, o.access_ts DESC

Hope that helps,
Regina

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Michael
Swierczek
Sent: Tuesday, February 12, 2008 9:30 AM
To: Andreas; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] 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.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
> >>
> >>
> >
> >
>
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.