CODEWITHSUNDEEP
sqlmysql
Jack Sleight
Jack Sleight

Reputation: 17118

Simple SQL problem (MySQL)

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.

Upvotes: 2

Views: 241

Answers (4)

Glenner003
Glenner003

Reputation: 1552

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

Upvotes: 0

DarkwingDuck
DarkwingDuck

Reputation: 2706

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

Upvotes: -1

Sophia
Sophia

Reputation: 5823

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.

Upvotes: 2

Rahul
Rahul

Reputation: 13056

Add a WHERE clause similar to

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

Upvotes: 5

Related Questions