PHP/MySQL -- Selecting latest revision of multiple records

Goodshape

I have a table containing text content for a website, multiple blog posts for instance. I want to maintain a record of past revisions -- so rather than replacing an existing record when changes are made, a new record is created with and the 'revision' number is incremented.

When viewing a single blog post I can simply say "order by revision desc, limit 1" to get the latest. The problem comes when viewing the "all posts" page. I want to get back ONE row for each post, and for that row to be the latest revision in each case.

If I do a 'group by url_title' I obviously get back the single row per post but this selects the first revision, not the latest.

Is what I'm attempting even possible, or should I rethink? Maybe move the past revisions to a separate table (seems like an annoying extra step)?

Any help, much appreciated

Goodshape

Actually... maybe I'll just throw in another column for 'active'. Set the latest revision active=1, past revisions active=0, then just select where active=1.

Seems like a nice and simple solution to the problem.

seamus

I asked a similar question here fado, fado and got an eminently workable and great answer from bonkey which I've been unable to better since.

You need a subquery to pull out a list of all post_id's, and the latest revision for each. Then you can use that subquery to filter your main table.

So, for example

SELECT 
	p.* 
FROM 
	posts p, 
	(SELECT
		postid,
		MAX(revision) as max_rev
	FROM posts
	group by postid) subq
WHERE 
	p.postid = subq.postid
	AND p.revision = subq.revision

Other techniques I've seen use a boolean column in the database which specifies for example, "is_latest_revision".

Simplifies and optimises the query no end, but it has additional overhead in making sure that the flag is correctly applied.

seamus

Heh, I see you came up with the boolean answer yourself.

The main issue you need to watch out for with such an approach is that two rows don't end up with the same ID and active = 1. Then how do you know which one is the active one?

You can mitigate this by implementing an additional table with two columns - the postid and the latest revision id. You can put a unique constraint on the postid so that only one revision id can ever be in that table for any post id.

Depending on the MySQL version, you could then implement UPDATE and INSERT triggers on your posts table to automatically keep this table in check.

Goodshape

Thanks seamus.

Funny how the mind ticks over to new ideas (the boolean method) once you get your thoughts written out

Subquery looks good too.. I'll see which of these methods works best for me.