Re: SQL query...

use alias for 'Count(file_name)' to use in order by clause F.x. "SELECT DISTINCT(file_name), Count(file_name) as file_count FROM $table_name WHERE date BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by file_count desc" Hope that solves it Nitin ----- Original Message ----- From: <Tristan.Pretty@risk.sungard.com> To: <php-db@lists.php.net> Sent: Thursday, January 15, 2004 4:31 PM Subject: [PHP-DB] SQL query... > "SELECT DISTINCT(file_name), Count(file_name) FROM $table_name WHERE date > BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by ??????? > desc" > > In the above sql statement, I'm trying to achieve: > > 1. select all file names, between two dates. > 2. list them, and order by the highest number of occurences of count() > > Basically, it's for a download tool we have, and my boss wants to easily > be able to see the top downloaded files. > It all works, but not the 'order by' bit... what do I have to order by... > it's not 'file_name', and 'order by count(file_name0' causes an > error... > > thoughts? > > Cheers, > Tris... > > ********************************************************************* > The information contained in this e-mail message is intended only for > the personal and confidential use of the recipient(s) named above. > If the reader of this message is not the intended recipient or an agent > responsible for delivering it to the intended recipient, you are hereby > notified that you have received this document in error and that any > review, dissemination, distribution, or copying of this message is > strictly prohibited. If you have received this communication in error, > please notify us immediately by e-mail, and delete the original message. > *********************************************************************** > >