Hi - I had a nagging feeling that there was a better solution to this than the temporary table-based solutions I saw; I created a table 'test' with the data you have below, and played with queries a bit. I came up with this, seems to work:
select a.web_account,a.code_short,sum(if(a.web_account=b.web_account,1,0)) as c from test as a,test as b group by concat(a.web_account,a.code_short) order by c desc; +-------------+------------+------+ | web_account | code_short | c | +-------------+------------+------+ | J009 | G | 3 | | J009 | U | 3 | | J009 | S | 3 | | A007 | U | 2 | | A007 | S | 2 | | B001 | U | 1 | +-------------+------------+------+ 6 rows in set (0.01 sec) -steve At 1:12 PM +0000 12/14/01, Girish Nath wrote: >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 > -- +------------------------------------------------------------------------+ | Steve Edberg [EMAIL PROTECTED] | | University of California, Davis (530)754-9127 | | Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ | +------------------------------------------------------------------------+ | "Restriction of free thought and free speech is the most dangerous of | | all subversions. It is the one un-American act that could most easily | | defeat us." | | - Supreme Court Justice (1939-1975) William O. Douglas | +------------------------------------------------------------------------+ --------------------------------------------------------------------- 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