Top posters SQL 1

[Shrum]

I have a database where I track users that post things.

I want to be able to list the top poster (field name: `submitter`) and the number of entries that are in the database.

What is the SQL for this sort of query?

TIA

=============================
Sean Shrum
Shrum Consulting

http://consulting.shrum.net

 

[Shrum]

I just re-read my post and even I got a bit confused. To clarify:

I have a db like:

id|submitter
1|sean
2|john
3|john
4|steve
5|sean
6|john

I want to generate a SQL statement that would produce a list of submitters and the number of entries in descending count order like:

submitter|COUNT
john|3
sean|2
steve|1

=============================
Sean Shrum
Shrum Consulting

http://consulting.shrum.net

 

[r937]

select submitter
, count(*)
from yourtable
group
by submitter
order
by 2 desc


rudy

 

[ggggus]

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

 

[ggggus]

Oops! I errored...

In that first query, it shold be ORDER BY num_posts DESC