Implementing Nested Aggregation in SQL-Server 2005

In order to implement nested aggregate functions in SQL-Server 2005, we have to use the existing options in a fine tuned manner.

We can’t directly use

Select  max(count(city) , country from dimgeography group by country

Now to implement this use the below query.

Select country,max(count(city))  over() from dimgeography group by country