Re: Subject: MySQL query question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux