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