Top users(comments)

I have a users table and a comments table and I want to select from users the top users that have the biggest amount of comments from the comments table and order them by numbers of comments

Just the forum comments should be counted(type can be forum,picture,news etc)

table structure

users

id | username | password

comments

id | text | type | au开发者_如何学编程thor_id

SELECT 
   u.id,u.username,u.password ,count(c.id) as total
FROM
   users u
JOIN comments c on c.author_id = u.id
GROUP BY u.id
ORDER BY total DESC;

This should do the trick, try and let us know how it worked out for you

UPDATE For details on how to do this you will fall inlove with this article

Update : New Link, old one is broken Databases

Tested on PostgreSQL 8.1:

select users.id, count(comments.author_id) 
from users, comments
where users.id = comments.author_id
group by users.id
order by 2 desc

SELECT users.id, users.name, COUNT(comments.id) AS cnt
FROM users
LEFT JOIN comments ON users.id = comments.author_id
WHERE type IN ('forum', 'picture', 'news', 'etc')
GROUP BY users.id
ORDER BY cnt DESC
LIMIT 10

Get the 10 most prolific commenter where the comment type is 'form', 'picture', 'news', or 'etc'.