34 messages in com.mysql.lists.mysqlRE: Order By number of rows returned ?
FromSent OnAttachments
Matthew Darcy12 Dec 2001 02:58 
Tony Buckley12 Dec 2001 03:29 
Carl Troein12 Dec 2001 03:49 
Etienne Marcotte12 Dec 2001 04:59 
Kelly Firkins12 Dec 2001 05:21 
Matthew Darcy12 Dec 2001 05:31 
Etienne Marcotte12 Dec 2001 05:38 
Thrill12 Dec 2001 05:38 
Matthew Darcy12 Dec 2001 06:21 
Christopher Schreiber12 Dec 2001 08:45 
John Meyer12 Dec 2001 11:19 
Quentin Bennett12 Dec 2001 11:54 
Alex Shi12 Dec 2001 14:45 
Mike Grabski12 Dec 2001 14:56 
Sinisa Milivojevic13 Dec 2001 04:53 
Alex Shi13 Dec 2001 11:50 
Etienne Marcotte13 Dec 2001 12:10 
Miguel Angel Solórzano13 Dec 2001 12:12 
Alex Shi13 Dec 2001 12:22 
Etienne Marcotte13 Dec 2001 12:50 
Ryan Fox13 Dec 2001 13:13 
Miguel Angel Solórzano13 Dec 2001 13:29 
Miguel Angel Solórzano13 Dec 2001 13:34 
j.urban13 Dec 2001 13:57 
Joel Wickard13 Dec 2001 14:22 
ST Ooi13 Dec 2001 15:49 
Vicente Castelló Ferrer14 Dec 2001 04:44 
Girish Nath14 Dec 2001 05:12 
Johnny Withers14 Dec 2001 07:03 
Girish Nath14 Dec 2001 07:29 
sherzodR14 Dec 2001 08:25 
Christopher Bergeron14 Dec 2001 16:49 
Girish Nath14 Dec 2001 17:33 
Steve Edberg15 Dec 2001 12:50 
Subject:RE: Order By number of rows returned ?
From:Johnny Withers (joh@mindbender.net)
Date:12/14/2001 07:03:08 AM
List:com.mysql.lists.mysql

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 joh@mindbender.net p. 601.853.0211 c. 601.209.4985

-----Original Message----- From: Girish Nath [mailto:giri@btinternet.com] Sent: Friday, December 14, 2001 7:13 AM To: mys@lists.mysql.com 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 <mysq@lists.mysql.com> To unsubscribe, e-mail <mysql-unsubscribe-johnny=mind@lists.mysql.com> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php