> 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@xxxxxxxxxx> wrote: > On Mon, Jan 26, 2015 at 2:38 PM, Tim Smith <randomdev4+postgres@xxxxxxxxx> > 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
