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

Re: Question regarding GROUP BY

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: Michael Swierczek <mike(dot)swierczek(at)gmail(dot)com>, ListaPostgre <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Question regarding GROUP BY
Date: 2008-02-12 01:59:08
Message-ID: 47B0FD6C.5030100@gmx.net (view raw or flat)
Thread:
Lists: pgsql-novice
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
>>
>>     
>
>   


In response to

Responses

pgsql-novice by date

Next:From: Michael FuhrDate: 2008-02-12 02:42:19
Subject: Re: [PERFORM] Question about CLUSTER
Previous:From: Andreas BurkhardtDate: 2008-02-12 01:35:40
Subject: user administration with photo and address

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