Re: Subject: MySQL query question

"Neil Smith [MVP, Digital media]" <php@xxxxxxxxxxxxxxxxxxxxxxxx> · Fri, 13 Feb 2004 14:18:21 +0000

I think you just have to 'alias' the result count, so that ORDER BY knows 
what to use to do the ordering - it needs a column name, but an alias is 
equivalent to a column name.

So :

$mostcomquery =         SELECT artid, COUNT(*) AS hitcount
                        FROM comments
                        GROUP BY artid
                        ORDER BY hitcount DESC

Should work fine.

HTH - Neil.

At 10:31 13/02/2004 +0000, you wrote:
Message-ID: <000c01c3f20c$fcb3d880$4fb97418@JMS>
From: "js" <james@xxxxxxxxxxxxx>
To: <php-db@xxxxxxxxxxxxx>
Date: Fri, 13 Feb 2004 02:40:08 -0600
MIME-Version: 1.0
Content-Type: multipart/alternative;
        boundary="----=_NextPart_000_0009_01C3F1DA.B1D6BA30"
Subject: MySQL query question

i want to search the table called comments, and i want to count the number 
of times artid appears. then i want to group them all together, but then i 
want to order them by most appearances to fewest. so if artid 1 appeared 
40 times and it was the most, i want that to be retrieved first. if artid 
3 appeared 38 times and it was second most, i want that retrieved 
second... etc and so on. this code ive given is wrong, but i know that it 
has to be something like this or that im close. if any of you could help 
me id really appreciate it. if theres a shortcut with this code that would 
be great, otherwise i have to run my php around in a ton of different 
mysql queries trying to find out which has the most and have it ordered 
from highest to lowest. thank you for your help. ive tried mysql.com too 
and i cant find a thing there. plus the mailing lists for mysql, no one 
ever responds! thank you php list, you are my only hope. ;P

-james

$mostcomquery = "SELECT artid, COUNT(*) FROM comments GROUP BY artid ORDER 
BY (COUNT(*)) DESC LIMIT 5";

========================================================
CaptionKit http://www.captionkit.com : Production tools
for accessible subtitled internet media, transcripts
and searchable video. Supports Real Player, Quicktime
and Windows Media Player.

VideoChat with friends online, get Freshly Toasted every
day at http://www.fresh-toast.net : NetMeeting solutions
for a connected world.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php