开发者

SQL for selecting only the last item from a versioned table

开发者 https://www.devze.com 2023-01-04 02:05 出处:网络
I have a table with the following structure: CREATE TABLE items ( id serial not null, title character varying(255),

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 int开发者_如何学Pythoneger,
    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?


SELECT distinct on (parent_id) 
    parent_id
  , version_id
FROM items
ORDER BY parent_id, version_id DESC
0

精彩评论

暂无评论...
验证码 换一张
取 消