Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Can I do this using SQL?

Re: Can I do this using SQL?

From: Tony Rogerson <tonyrogerson_at_sqlserverfaq.com>
Date: Fri, 10 Feb 2006 08:34:51 -0000
Message-ID: <dshj6k$7d8$1$8302bc10@news.demon.co.uk>


Hi,

Something along the lines of this might work if you play with it, the problem comes where you have the same number of customers at each branch ->

select *
from (

    select oq.country,

           oq.country_count,
           row_number = ( select count(*)
                          from (    select country,
                                           country_count = count(*)
                                    from registrations
                                    group by country ) as oq3
                          where oq3.country_count > oq.country_count )
    from (
        select country,
               country_count = count(*)
        from registrations
        group by country ) as oq

    ) as res
where res.row_number <= 10
order by res.row_number

Tony.

-- 
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"dhruv" <dhruvbird_at_gmail.com> wrote in message 
news:1139486157.184995.202500_at_g43g2000cwa.googlegroups.com...

> Hello,
> Maybe I'm missing something, but I can't figure out a way to write an
> SQL query for the floowing requirements:
>
> I have a table which holds accounts of customers. Now, the schema is as
> follows:
>
> CUST_NAME, BRANCH_NAME, BALANCE, CUST_ID
> [ok, yes, it's not _actually_ like this, but this is the result of a
> join on some obviously normalized tables].
>
> Now, I want to get a list of the TOP 10 Branches wrt. the number
> customers they have. How would I do that?
>
> First I thought grouping or sorting would help, but sort on frequencey?
> How do I do that in SQL?
>
> -Dhruv.
>
Received on Fri Feb 10 2006 - 02:34:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US