Okay, I’m working fixing bugs in AqWiki before I release, and one of the major ones is that currently the Search function requires MySQL4’s subqueries (Actually, the whole thing requires MySQL at the moment, but that’s a post-1.0 issue) and I’m trying to de-subquery the query, but it’s not working, so I’m throwing it out in the open to see what happens.

This works, but relies on Subqueries which I want to avoid (Or at least support people not having)

SELECT wikipage.page, wikipage.name, wikipage.created,
	max(revision.created) as revised, revision.revision
 FROM revision
 LEFT JOIN
	wikipage ON wikipage.page = revision.page
	and revision.revision =
	(SELECT max(r2.revision)
		from revision as r2
		where r2.page = revision.page)

 WHERE content LIKE "%TODO%"
	AND wiki = "personal"
 GROUP BY wikipage.page;

This doesn’t work (the second AND statement isn’t valid) but it’s basically what I want to do:

SELECT wikipage.page, wikipage.name, wikipage.created,
revision.created as revised, revision.revision, max(revision.revision) as toprev
 FROM revision
 LEFT JOIN
	wikipage ON wikipage.page = revision.page
 WHERE content LIKE "%TODO%" AND wiki = "personal"
	AND revision.revision = max(revision.revision)
 GROUP BY wikipage.page
 ORDER BY revision.revision desc;

Any ideas? (Yes, I know I should use fulltext instead of LIKE, that’s next on the Big List Of Bugfixen Before We Go Live)

In case it helps, the Table structure is now online