SELECT users.user_id, users.user_name, Count(*) as num_posts
FROM users, messages
WHERE users.user_id=messages.user_id
GROUP BY user_id
ORDER BY num_patterns DESC
LIMIT 0,20
Here's what I use in my forum, some of this will depend on the structure of your forum tables.
Now I have a question along the same lines...
I want to rate user participation, and on the site, users can post, and upload different items. In this case they can upload 3d objects, and patterns. I want to give users 15 points for objects uploaded, 10 points for patterns uploaded, and 1 point for posts in the forum. Can I calculate all of this in one query?
currenly I'm working on the page I started with the above query for posts, and the following two for the other tables...and am going to calculate it external to the query.
SELECT users.user_id, users.user_name, Count(*) as num_patterns
FROM users, patterns
WHERE users.user_id=patterns.upload_user_id
GROUP BY user_id
ORDER BY num_patterns DESC
LIMIT 0,20
SELECT users.user_id, users.user_name, Count(*) as num_families
FROM users, families
WHERE users.user_id=families.upload_user_id
GROUP BY user_id
ORDER BY num_families DESC
LIMIT 0,20
By the way, the objects are called families.
Thanks