Help with a query plz

[2oMst]

Ok i tried helping out a guy about a ranking system he tried to do on his site and couldn\'t quite get the query working.

 

What I want:

I have a forum_1_msg table with a user column.

I would like to have a ranking system of the most posts.

A post = 1 row in that table.

 

In theory:

Display every user once, using DISTINCT and count how many times this user has posted on the forum, and then sort it.

 

A few queries I tried:

 

mysql_query("SELECT DISTINCT user, count(user) AS nbr_posts FROM forum_1_msg ORDER BY nbr_posts DESC");



mysql_query("SELECT GROUP_CONCAT(DISTINCT user) AS cur_user, GROUP_CONCAT(count(cur_user)) AS nbr_posts FROM forum_1_msg ORDER BY nbr_posts DESC");

 

I tried many other queries, the number 1 I thought would be the most accurate. After reading the doc on mysql.com I thought the number 2 query would be more appropriate but hey .... Both don\'t work.

 

Any help here ???

 

PS: I know this is not the best way for this to work but it\'s mainly for educational purpose on mysql_querying

 

Thank you

[Barand]

SELECT user, COUNT(*) as nbr_posts FROM forum_1_msg

GROUP BY user ORDER BY nbr_posts DESC

 

hth

[2oMst]

ahh !! yes, very nice

 

GROUP BY power

 

I\'ll experiment with group by, thank you.

 

ps: anyone know of any good sites showing all the syntax you can put into a mysql_query ... ?? (besides mysql.com)