SQL for selecting only the last item from a versioned table
- by Jeremy
I have a table with the following structure:
CREATE TABLE items (
id serial not null,
title character varying(255),
version_id integer DEFAULT 1,
parent_id integer,
CONSTRAINT items_pkey PRIMARY KEY (id)
)
So the table contains rows that looks as so:
id: 1, title: "Version 1", version_id: 1, parent_id: 1
id: 2, title: "Version 2", version_id: 2, parent_id: 1
id: 3, title: "Completely different record", version_id: 1, parent_id: 3
This is the SQL code I've got for selecting out all of the records with the most recent version ids:
select * from items
inner join (
select parent_id, max(version_id) as version_id from items
group by parent_id) as vi
on items.version_id = vi.version_id and items.parent_id = vi.parent_id
Is there a way to write that SQL statement so that it doesn't use a subselect?
