ORDER BY function() 1

[donutzeatpeople]

It seems that whenever I try to make a query with ORDER BY COUNT(field) I always recieve an error from MySQL. Here is the specific query I am trying to use:

SELECT u.username,COUNT(m.entryid) FROM users as u,messages as m WHERE m.userid = u.userid GROUP BY u.userid ORDER BY COUNT(m.entryid) LIMIT 0,10

There must be an easy solution that I just don't see. Can anyone help? Thank you!

 

[kasuals]

What is the error?

http://www.angsttechnology.com

- "Delightfully confusing..." raves The New York Times

-kas

 

[sleipnir214]

You can't use an aggregation function in an ORDER BY clause. You can, however, assign an alias to the derived column and use that alias in the ORDER BY clause:

SELECT u.username,COUNT(m.entryid) as thecount[/blue]
FROM users as u, messages as m
WHERE m.userid = u.userid
GROUP BY u.userid
ORDER BY thecount[/blue]
LIMIT 0,10

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!