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_at_news.demon.co.uk>
) as res
where res.row_number <= 10
order by res.row_number
Date: Fri, 10 Feb 2006 08:34:51 -0000
Message-ID: <dshj6k$7d8$1$8302bc10_at_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...Received on Fri Feb 10 2006 - 09:34:51 CET
> 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.
>