List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:December 15 2001 8:50pm
Subject:Re: Order By number of rows returned ?
View as plain text  
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                                      sbedberg@stripped |
| 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 |
+------------------------------------------------------------------------+
Thread
RE: A News Group Perhaps.Matthew Darcy12 Dec
  • Re: [OT] A News Group Perhaps.Tony Buckley12 Dec
    • Re: A News Group Perhaps.Carl Troein12 Dec
      • I must be mental but.Matthew Darcy12 Dec
        • RE: I must be mental but.Matthew Darcy12 Dec
          • Why MySQL GUI download don't work?Alex Shi12 Dec
            • Re: Why MySQL GUI download don't work?Sinisa Milivojevic13 Dec
            • RE: Why MySQL GUI download don't work?Vicente Castelló Ferrer14 Dec
              • Order By number of rows returned ?Girish Nath14 Dec
                • RE: Order By number of rows returned ?Johnny Withers14 Dec
                  • Re: Order By number of rows returned ?Girish Nath14 Dec
                    • Re: Order By number of rows returned ?sherzodR14 Dec
                      • RE: Order By number of rows returned ?Christopher Bergeron15 Dec
                        • Re: Order By number of rows returned ?Girish Nath15 Dec
                • Re: Order By number of rows returned ?Steve Edberg15 Dec
      • Re: Anti-SPAM (was: A News Group Perhaps)Thrill12 Dec
  • Re: [OT] A News Group Perhaps.Etienne Marcotte12 Dec
Re: [OT] A News Group Perhaps.Kelly Firkins12 Dec
  • Re: [OT] A News Group Perhaps.Etienne Marcotte12 Dec
  • RE: [OT] A News Group Perhaps.Christopher Schreiber12 Dec
    • RE: [OT] A News Group Perhaps.John Meyer13 Dec
  • MySQL on Win2000Alex Shi13 Dec
    • Re: MySQL on Win2000Miguel Angel Solórzano13 Dec
      • Re: MySQL on Win2000j.urban13 Dec
        • Re: MySQL on Win2000ST Ooi14 Dec
  • Re: MySQL on Win2000Etienne Marcotte13 Dec
  • Re: MySQL on Win2000Alex Shi13 Dec
    • Re: MySQL on Win2000Miguel Angel Solórzano13 Dec
  • Re: MySQL on Win2000Joel Wickard13 Dec
  • Re: MySQL on Win2000Etienne Marcotte13 Dec
    • Re: MySQL on Win2000Miguel Angel Solórzano13 Dec
  • Re: MySQL on Win2000Ryan Fox13 Dec
RE: Anti-SPAM (was: A News Group Perhaps)Quentin Bennett12 Dec
RE: I must be mental but.Mike Grabski12 Dec