Hmmm....running MySQL 3.22.32 on actual server :( No temporary heap tables :(
Girish ----- Original Message ----- From: "Christopher Bergeron" <[EMAIL PROTECTED]> To: "MySQL" <[EMAIL PROTECTED]> Sent: Saturday, December 15, 2001 12:49 AM Subject: RE: Order By number of rows returned ? > Creating temporary _fileS_ will create a lot of disk unnecessary disk i/o. > depending on how "busy" your DB is, I would use tables in memory vs. files > on disk... > > just my .02c > -CB > > -----Original Message----- > From: sherzodR [mailto:[EMAIL PROTECTED]] > Sent: Friday, December 14, 2001 11:25 AM > To: Girish Nath > Cc: Johnny Withers; [EMAIL PROTECTED] > Subject: Re: Order By number of rows returned ? > > > > Create temporary files instead, in that case you don't have to worry > about DROPing the tables, they will get dropped as soon as the mysql > session is closed. > > > Girish Nath wrote: > > GN: Date: Fri, 14 Dec 2001 15:29:38 -0000 > GN: From: Girish Nath <[EMAIL PROTECTED]> > GN: To: Johnny Withers <[EMAIL PROTECTED]>, [EMAIL PROTECTED] > GN: Subject: Re: Order By number of rows returned ? > GN: > GN: Hi > GN: > GN: Thanks for that, it works really well :) > GN: > GN: Best Regards > GN: > GN: > GN: Girish > GN: > GN: > GN: ----- Original Message ----- > GN: From: "Johnny Withers" <[EMAIL PROTECTED]> > GN: To: "'Girish Nath'" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > GN: Sent: Friday, December 14, 2001 3:03 PM > GN: Subject: RE: Order By number of rows returned ? > GN: > GN: > GN: > I'm not sure if you can do this all in one query.. > GN: > I tried a few JOINs, and nothing seemed to work. > GN: > However, I'm not up to speed on how to join things > GN: > together to get the best results. > GN: > > GN: > However, you can do it by creating a temp table: > GN: > > GN: > create table tmp01( > GN: > web_account char(4) not null default '####', > GN: > count_wa int unsigned not null default 0 > GN: > ); > GN: > > GN: > INSERT INTO tmp01(web_account,count_wa) > GN: > SELECT web_account,count(web_account) AS count_wa > GN: > FROM lookup > GN: > GROUP BY web_account > GN: > ORDER BY count_wa DESC; > GN: > > GN: > SELECT lookup.web_account,lookup.code_short > GN: > FROM lookup,tmp01 > GN: > WHERE (lookup.web_account=tmp01.web_account) > GN: > ORDER BY tmp01.count_wa DESC; > GN: > > GN: > DROP table tmp01; > GN: > > GN: > > GN: > This is probably not the best solution to your problem. > GN: > > GN: > --------------------- > GN: > Johnny Withers > GN: > [EMAIL PROTECTED] > GN: > p. 601.853.0211 > GN: > c. 601.209.4985 > GN: > > GN: > -----Original Message----- > GN: > From: Girish Nath [mailto:[EMAIL PROTECTED]] > GN: > Sent: Friday, December 14, 2001 7:13 AM > GN: > To: [EMAIL PROTECTED] > GN: > Subject: Order By number of rows returned ? > GN: > > GN: > > GN: > Hi > GN: > > GN: > I'm trying to do some sorting by relevance on a query. > Essentially, i'd > GN: > like > GN: > to know if there is way to order the results by number of rows > returned > GN: > or > GN: > if this is the best i can get and do the rest within PHP? > GN: > > GN: > mysql> SELECT web_account, code_short FROM lookup WHERE code_short > IN > GN: > ('U', > GN: > 'S', 'G'); > GN: > > GN: > +-------------+------------+ > GN: > | web_account | code_short | > GN: > +-------------+------------+ > GN: > | A007 | U | > GN: > | A007 | S | > GN: > | J009 | G | > GN: > | J009 | U | > GN: > | J009 | S | > GN: > | B001 | U | > GN: > +-------------+------------+ > GN: > 6 rows in set (0.00 sec) > GN: > > GN: > I'd like to order these so that "J009" would be grouped at the top > of > GN: > the > GN: > set because it was found in 3 rows, "A007" would be placed after > "J009" > GN: > with > GN: > "B001" last. > GN: > > GN: > Any ideas :) ? > GN: > > GN: > Thanks for your time. > GN: > > GN: > > GN: > > GN: > Girish > GN: > > GN: > > GN: > > --------------------------------------------------------------------- > GN: > Before posting, please check: > GN: > http://www.mysql.com/manual.php (the manual) > GN: > http://lists.mysql.com/ (the list archive) > GN: > > GN: > To request this thread, e-mail <[EMAIL PROTECTED]> > GN: > To unsubscribe, e-mail > GN: > <[EMAIL PROTECTED]> > GN: > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > GN: > > GN: > > GN: > > GN: > > --------------------------------------------------------------------- > GN: > Before posting, please check: > GN: > http://www.mysql.com/manual.php (the manual) > GN: > http://lists.mysql.com/ (the list archive) > GN: > > GN: > To request this thread, e-mail <[EMAIL PROTECTED]> > GN: > To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> > GN: > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > GN: > > GN: > GN: > GN: > > GN: --------------------------------------------------------------------- > GN: Before posting, please check: > GN: http://www.mysql.com/manual.php (the manual) > GN: http://lists.mysql.com/ (the list archive) > GN: > GN: To request this thread, e-mail <[EMAIL PROTECTED]> > GN: To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> > GN: Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > GN: > > -- > Sherzod Ruzmetov <[EMAIL PROTECTED]> > http://www.UltraCgis.com, Consultant > 989.774.6265 > +----------------------------------------+ > | There is nothing wrong with your tools.| > | But we can make a better one. | > +----------------------------------------+ > > > --------------------------------------------------------------------- > 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 > --------------------------------------------------------------------- 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
