Select MAX within join?

SELECT
tblPageItems.id,
tblPageItems.item_name,
tblPageItems.item_category,
tblItemVersions.html
FROM tblPageItems
INNER JOIN tblItemVersions ON tblItemVersions.page_item_id = tblPageItems.id
If there's more than one entry in tblItemVersions with a particular Item ID - as there will be, this is for a CMS with version control - then the query above will return a row for each version of each item found.

I don't want that. I just want a row for each Item ID, containing the latest (ie: highest ID) corresponding html field from tblItemVersions. And I'm having a mental block, and can't figure it out. Anyone? :confused:

Sorry - this post has been retracted due to lack of caffeine.

Daemonspyre

That won't work as your group by clause is incorrect. A group by clause must contain each non-aggregate in the select clause. After you add those you will get more than one row per id.

Spudhead

Read this article (http://guelphdad.wefixtech.co.uk/sqlhelp/latestfromgroup.shtml).

That's it! Thank you :)

ETA:

Ah. Not quite. I'm still floundering:

SELECT html FROM tblitemversions as versions
WHERE versions.id = (
SELECT MAX(id)
FROM tblitemversions
WHERE tblitemversions.page_item_id = versions.page_item_id)

That gives me the following error:
You have an error in your SQL syntax near 'SELECT MAX(id) FROM tblitemversions WHERE tblitemversions.page_item_id = version' at line 1

And that's just a bit to pull the latest version - I've still got to hook it up to the bit that joins the Items table. I'm very confused :(

(this is against MySQL 4.0, through phpMyAdmin, on localhost)

upgrade to mysql 4.1 it supports subqueries.

Ah. Yes, that would probably do it. Never mind, it was only a whole day I spent banging my head on that one :o Thanks for your help :)