How to limit GROUP BY clause responses in sql-server (2005)

Question

I try to get the 50 best cities for all customer lists in our database (It’s so simplified: each customer has a customer list with related data (for example, city))

If I say:

SELECT top(50) clientid, city, COUNT(city) as cnt FROM customers GROUP BY clientid, city ORDER by cnt 

it will limit the overall result set to 50 rows instead of limiting the results for each group.

How can I get the top 50 customers?

EDIT: I was looking for stackoverflow (and googled), but found solutions for Mysql. Probably a “limit” search will only find mysql solutions, and this is the keyword needed for this database engine. If I know the keyword needed for Sql-Server, I could find it using google as well.

Answer 1

 ;WITH cte As (SELECT clientid, city, COUNT(city) as cnt, ROW_NUMBER() OVER (PARTITION BY clientid ORDER BY COUNT(city)) AS RN FROM customers GROUP BY clientid, city) SELECT clientid, city FROM cte WHERE RN <= 50 

Answer 2

I think this can do it:

 select top 50 city from (select city from customers group by city order by count(clientid) desc) 

I assume that if the customer ID is on the same line as the city, then this customer ID represents a user residing in that city.