I'm not sure if you can do this all in one query.. I tried a few JOINs, and nothing seemed to work. However, I'm not up to speed on how to join things together to get the best results.
However, you can do it by creating a temp table: create table tmp01( web_account char(4) not null default '####', count_wa int unsigned not null default 0 ); INSERT INTO tmp01(web_account,count_wa) SELECT web_account,count(web_account) AS count_wa FROM lookup GROUP BY web_account ORDER BY count_wa DESC; SELECT lookup.web_account,lookup.code_short FROM lookup,tmp01 WHERE (lookup.web_account=tmp01.web_account) ORDER BY tmp01.count_wa DESC; DROP table tmp01; This is probably not the best solution to your problem. --------------------- Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -----Original Message----- From: Girish Nath [mailto:[EMAIL PROTECTED]] Sent: Friday, December 14, 2001 7:13 AM To: [EMAIL PROTECTED] Subject: Order By number of rows returned ? Hi I'm trying to do some sorting by relevance on a query. Essentially, i'd like to know if there is way to order the results by number of rows returned or if this is the best i can get and do the rest within PHP? mysql> SELECT web_account, code_short FROM lookup WHERE code_short IN ('U', 'S', 'G'); +-------------+------------+ | web_account | code_short | +-------------+------------+ | A007 | U | | A007 | S | | J009 | G | | J009 | U | | J009 | S | | B001 | U | +-------------+------------+ 6 rows in set (0.00 sec) I'd like to order these so that "J009" would be grouped at the top of the set because it was found in 3 rows, "A007" would be placed after "J009" with "B001" last. Any ideas :) ? Thanks for your time. Girish --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
