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
