From: | Tim Smith <randomdev4+postgres(at)gmail(dot)com> |
---|---|
To: | Maciek Sakrejda <maciek(at)heroku(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Versioning Schema SQL ideas needed |
Date: | 2015-01-27 08:54:45 |
Message-ID: | CA+HuS5EccVTC5LvAnhtQrU5XqNgj-abAxwfBSQP8TSW9W3tS5Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> DISTINCT ON is my favorite lesser-known Postgres feature.
You said it ! There I was, trying and failing to make "DISTINCT" work
for me, little did I know that the little word ON was missing from my
Postgresql vocabulary !
Thanks !
On 27 January 2015 at 02:24, Maciek Sakrejda <maciek(at)heroku(dot)com> wrote:
> On Mon, Jan 26, 2015 at 2:38 PM, Tim Smith <randomdev4+postgres(at)gmail(dot)com>
> wrote:
>>
>> create table templates(
>> template_id int not null primary key,
>> template_groupid int not null,
>> template_version int not null
>> template_text text not null);
>>
>> Would I need to resort to using a CTE or subquery to make this sort of
>> thing work ? I can't seem to make it work with group by since group
>> by expects aggregation. Surely I don't need to normalise it into a
>> couple of tables ?
>
>
>
> What sort of thing? Selecting the latest version of each template? Try
>
> SELECT
> DISTINCT ON (template_group_id) template_id, template_group_id,
> template_version, template_text
> FROM
> templates
> ORDER BY
> template_group_id, template_version DESC
>
> You could even create a latest_templates view for this. DISTINCT ON is my
> favorite lesser-known Postgres feature.
From | Date | Subject | |
---|---|---|---|
Next Message | Christian Paminger | 2015-01-27 09:19:41 | Re: multiple parameters to an AGGREGATE function |
Previous Message | Tim Smith | 2015-01-27 08:53:01 | Re: In need of some JSONB examples ? |