Thread: Question regarding GROUP BY
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
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 >> >> > >
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 > >> > >> > > > > > >
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.