Simple SQL problem (MySQL)

Question

Hey, I have this query:

SELECT page.id, revision.title, revision.number
FROM page
    INNER JOIN revision ON page.id = revision.pageId

Which will return something like:

"1"   "Page Version A"   "1"
"1"   "Page Version B"   "2"
"1"   "Page Version C"   "3"

Now I only want to return one row for each page, with the data from the latest (highest numbered) revision. If I do:

SELECT page.id, revision.title, revision.number
FROM page
    INNER JOIN revision ON page.id = revision.pageId
GROUP BY page.id

I get:

"1"   "Page Version A"   "1"

But I want:

"1"   "Page Version C"   "3"

Any ideas? Thanks.

Answer 1

Add a WHERE clause similar to

WHERE revision.number = (select max(number) from revision r where r.pageId = page.id)

Answer 2

If you just want to select a single record you could use the MySQL 'LIMIT' keyword (similar to MSSQL 'TOP')

eg:

SELECT page.id, revision.title, revision.number
FROM page
    INNER JOIN revision ON page.id = revision.pageId

ORDER BY revision.number DESC
LIMIT 0, 1

This will order all of your results, then select the top result.

Answer 3

SELECT page.id, MAX(revision.number)
FROM page
INNER JOIN revision ON page.id = revision.pageId
GROUP BY page.id

Answer 4

If this is a query you will run many times, I would consider to make a view which selects the most recent revision per pageid:

create view LatestRevision
as 
Select pageid, max(number) from revision

then the query would be

SELECT page.id, revision.title, revision.number
FROM page
    INNER JOIN revision ON page.id = revision.pageId
    INNER JOIN LatestRevision on revision.pageid = LatestRevision.pageid and revision.Number = LatestRevision.number