SQL Question

[mpettitt]

I want to be able to pull the title of the latest entry from each category within a given section in the CMS we are using. The relevant table structure is (there are other columns, but they contain data that isn't related to this query, eg. creator, last modified timestamp, the article content

Comments

[mpettitt]

SELECT c.id, c.title, c.catid, t.title
FROM content as c
JOIN categories as t on t.id=c.catid
WHERE sectionid=1
group by t.title
having c.created=max(c.created)

gives me a "Unknown column 'c.created' in having clause" error.

It doesn't like it if I completely strip the aliases either, nor if I strip the aliases from just the having clause. (incidentally, this is MySQL 5, if that affects things?)

[mpettitt]

select c.id, c.title, c.catid, t.title
from content as c, categories as t
where t.id=c.catid and c.sectionid=1 and c.created = (select created from content where catid=t.id order by created desc limit 1)
group by t.id
order by c.created desc

Seems to do the trick, but it does rely on a subquery, which isn't ideal (I get more queries from people who are running old versions of MySQL wanting to know why various features of the module don't work for them), so a version that avoids the subquery would still be very useful.