$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: <[EMAIL PROTECTED]> From: "js" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> 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
$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