query problem

[PieAndChips]

Hi, can someone suggest why this query isnt working?

SELECT thread.threadid, COUNT(post.postid) AS posts
FROM thread
INNER JOIN post ON thread.threadid = post.threadid
WHERE (UNIX_TIMESTAMP()-post.dateline) < 86400
GROUP BY thread.threadid, COUNT(post.postid)
ORDER BY COUNT(post.postid) DESC

when i test it in phpMyAdmin i get an invalid use of group function error.

cheers.

[Xenon]

you can't group by threadid and a COUNT() value the same time..
i also think you cannot group by a count() value at all

[PieAndChips]

after a bit of fiddling about i got it working

SELECT
thread.threadid,
thread.title,
thread.lastposter,
COUNT(post.postid) AS numposts
FROM thread
INNER JOIN post ON thread.threadid = post.threadid
WHERE (UNIX_TIMESTAMP()-post.dateline) < 86400
GROUP BY thread.threadid
ORDER BY numposts DESC LIMIT 10

btw, this will give the 10 most active threads during the past 24 hours. :banana: