I'm Confused..

[Joshua F]

I have a MYSQL Query and when I see the code, I think it would do what I want it to do, also so do my other friends, but it doesn't.

 

What I'm trying to do is basically make a top 10 poster, and select and order the list by the person with the most posts in the table. The table has a field named user, which represents the username. So basically I'm trying to order it by the `user` with the most posts. The problem is that it only displays the first row in the table, which is id:1.

 

SELECT * FROM `mrsw_adventurer_log` ORDER BY COUNT(`user`)

 

I can't seem to figure it out.

[fxuser]

I have a MYSQL Query and when I see the code, I think it would do what I want it to do, also so do my other friends, but it doesn't.

 

What I'm trying to do is basically make a top 10 poster, and select and order the list by the person with the most posts in the table. The table has a field named user, which represents the username. So basically I'm trying to order it by the `user` with the most posts. The problem is that it only displays the first row in the table, which is id:1.

 

SELECT * FROM `mrsw_adventurer_log` ORDER BY COUNT(`user`)

 

I can't seem to figure it out.

 

whats the error your getting?

 

try

SELECT * FROM mrsw_adventurer_log ORDER BY user DESC

[Joshua F]

I have a MYSQL Query and when I see the code, I think it would do what I want it to do, also so do my other friends, but it doesn't.

 

What I'm trying to do is basically make a top 10 poster, and select and order the list by the person with the most posts in the table. The table has a field named user, which represents the username. So basically I'm trying to order it by the `user` with the most posts. The problem is that it only displays the first row in the table, which is id:1.

 

SELECT * FROM `mrsw_adventurer_log` ORDER BY COUNT(`user`)

 

I can't seem to figure it out.

 

whats the error your getting?

 

try

SELECT * FROM mrsw_adventurer_log ORDER BY user DESC

That code just repeats that specific user how many times his name is in the table.

[DavidAM]

You have to count the number of posts (or whatever) for each user and order by that count. Since you have no GROUP BY in that query, the COUNT will be 1 for each row. I don't know the layout of your table, but the query should be something like this:

 

SELECT user, COUNT(post_id) -- Count the primary key on the table
FROM post_table
GROUP BY user
ORDER BY COUNT(post_id) DESC -- show the user with the highest count first
LIMIT 10; -- only return 10 rows

[Joshua F]

You have to count the number of posts (or whatever) for each user and order by that count. Since you have no GROUP BY in that query, the COUNT will be 1 for each row. I don't know the layout of your table, but the query should be something like this:

 

SELECT user, COUNT(post_id) -- Count the primary key on the table
FROM post_table
GROUP BY user
ORDER BY COUNT(post_id) DESC -- show the user with the highest count first
LIMIT 10; -- only return 10 rows

 

Ooh, Thank you very much. The final code turned out to be the following.

SELECT user, COUNT( id )
FROM `mrsw_adventurer_log`
GROUP BY user
ORDER BY COUNT( id ) DESC
LIMIT 10