order by and gorup by in single sql statement

[skywaves]

Hi i have a table subforum with structure as follows

subforumid - int auto increment
forumid -int
userid -int
projectid -int
subject - longtext
createdate - datetime
deleted - int

now i want to display the number of replies and the last post's time.

select createdate, forumid, count(subforumid) from subforum  group by forumid order by createdate desc

it runs but does not give expected result. it order the createdate in result created by

select createdate, forumid, count(subforumid) from subforum  group by forumid

i know i am doing something wrong here but cannot figure out.
do i have to use multiple queris?

thanks

[Old Pedant]

Ahhh, you are going to kick yourself over this one.

SELECT forumid, MAX(createdate) AS lastCreatedSubforum, COUNT(*) AS numberOfSubforums
FROM subforum
GROUP BY forumid
ORDER BY lastCreatedSubforum DESC

You were done in by a "feature" of MySQL. Most DBs would have given you an error from that query, because you did GROUP BY *only* of forumid and did not put createdate into either an aggregate function or into the group by. MySQL is lenient, but then doesn't give you the answer you expect.