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.
