Hi Thanks for that, it works really well :)
Best Regards Girish ----- Original Message ----- From: "Johnny Withers" <[EMAIL PROTECTED]> To: "'Girish Nath'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, December 14, 2001 3:03 PM Subject: RE: Order By number of rows returned ? > 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 > --------------------------------------------------------------------- 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
