From: | Michael Gould <mgould(at)isstrucksoftware(dot)net> |
---|---|
To: | bruno(at)wolff(dot)to |
Cc: | lxr(at)mac(dot)com, pgsql-general(at)postgresql(dot)org |
Subject: | Re: GROUP BY or alternative means to group |
Date: | 2012-04-09 17:55:04 |
Message-ID: | s2pbp5uj13gu49i9dukruesl.1333994104699@email.android.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks that is a help. I would be nice if any key could be used as those are normally the things I would do group by's
Regards
Mike Gould
From my Samsung Android tablet on T-Mobile. The first nationwide 4G networkBruno Wolff III <bruno(at)wolff(dot)to> wrote:On Mon, Mar 12, 2012 at 16:18:05 -0400,
Michael Gould <mgould(at)isstrucksoftware(dot)net> wrote:
>You need to include all columns that are not aggregrative columns in the group by. Even though that is the standard it is a pain to list all columns even if you don't need them
In later versions of postgres this is relaxed a bit. If you are grouping
by a primary key, you don't need to group by columns that are fixed
by that key. For example the following query is accepted in 9.1 as gameid
is a key for games and hence we don't need to also group by ga,es.title.
SELECT games.gameid, games.title
FROM games, crate
WHERE
games.gameid = crate.gameid
AND
games.contact = 'BOB'
AND
crate.touched >= current_timestamp + '4 year ago'
GROUP BY games.gameid
HAVING count(1) < 30
ORDER BY games.gameid
;
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2012-04-09 18:28:07 | Re: GROUP BY or alternative means to group |
Previous Message | Tom Lane | 2012-04-09 17:44:50 | Re: My main/base/pgsql_tmp directory has over 5.7 millions files |