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
