[SOLVED] mysql complicated query help:

[ted_chou12]

This time, I wish to order the users by the no. of posts they posted, the forum table looks roughly like this:

id

username

1user1

2user2

3user1

4user1

5user2

 

The ID doesnt really matter, what I want to do is to organize the users according to the number of posts they have made, so the output looks like:

username

posts

user1          3

user2          2

where left is the username and right is the no of posts

Thanks

Ted

[Lodar]

The quickest way would be to modify the table structure you have there to include a new column called num_posts which is incremented each time the user makes a new post.

 

Then you can order the query results on the num_posts column

 

so your final table structure will look like

 

  id username num_posts

 

Its the way I have always done mine, and cuts down on potentially long queries finding all the posts that the user made in the database

[o3d]

This query should do the trick.

 

select username, count(*) as posts

from table

group by username

order by posts desc

[fenway]

The quickest way would be to modify the table structure you have there to include a new column called num_posts which is incremented each time the user makes a new post.

 

Then you can order the query results on the num_posts column

 

so your final table structure will look like

 

  id username num_posts

 

Its the way I have always done mine, and cuts down on potentially long queries finding all the posts that the user made in the database

This way is quick, but unless you're dealing with InnoDB tables, summary tables/columns are expensive, and prone to errors.

[Lodar]

ok thanks for updating me, its good to know.