I have something like this:
id title url revision
-------------------------------------
1 blah someurl 1
2 blah someurl 2
3 other someotherurl 1
4 other someotherurl 2
So I have two revisions of two different entries. Is there a way, through SQL, to SELECT just the latest revision of the latest documents?
IE: The query would return rows 2 and 4 because they're the latest revisions, and it would exclude selecting older revisions.
Use:
SELECT x.*
FROM YOUR_TABLE x
JOIN (SELECT t.title,
MAX(t.revision) AS max_rev
FROM YOUR_TABLE t
GROUP BY t.title) y ON y.title = x.title
AND y.max_rev = x.revision
Because MySQL doesn't support analytic functions (ROW_NUMBER), this approach is flawed -- if you have the same title, and more than one of those has the same lowest revision, all of those with that lowest revision for that title will be displayed.
SELECT title , max(revision)
FROM your_table
GROUP BY title
I am just unsure if it should be titles or urls in the query
When title and url are the same for every revision then use
SELECT y.* FROM (
SELECT MAX(revision) AS revision,title,url FROM <TABLE> GROUP BY title,url
) x INNER JOIN <TABLE> y ON x.title=y.title AND x.url=y.url AND x.revision=y.revision