MySQL statement to find the latest revision of nodes?
Hello, everybody. I've got a Drupal development question that's been stumping me for a while.
I am developing a Drupal 5 site with MySQL 4.x, and one of the features of the site is that users can be "associated" with a node. I track these associations with a new table, user_associations, that simply stores a user ID and a node ID.
To display a list of all users and their associated nodes, I use a pager query:
SELECT u.uid, u.name, n.title
FROM {user} u
INNER JOIN {user_association} ua ON u.uid = ua.uid
INNER JOIN {node} n ON n.nid = ua.nid
WHERE u.status = 1The problem comes when I start creating revisions of nodes; I end up getting multiple matches on the INNER JOIN {node}, which results in duplicate rows in the result (one for each revision).
However, I can't figure out how to edit my query to match only the latest revision of a node. I want something like
SELECT u.uid, u.name, n.title, MAX(nr.vid) AS latest_vid
FROM {user} u
INNER JOIN {user_association} ua ON u.uid = ua.uid
INNER JOIN {node_revisions} nr ON nr.nid = ua.nid
INNER JOIN {node} n ON n.vid = latest_vid
WHERE u.status = 1...but this doesn't work, because using MAX() requires a GROUP BY statement, which won't work if I'm using a pager query. I was told that I could do this with stored procedures in MySQL 5.0, but I'm using MySQL 4.x.
Any help would be appreciated!
--R.J.