Re: select top_countries and for each country, select top_cities in that country, in 1 query

Thanks John.

On Thu, Aug 28, 2014 at 2:35 PM, John McKown <john(dot)archie(dot)mckown(at)gmail(dot)com>
wrote:

> On Mon, Aug 18, 2014 at 10:52 AM, John McKown
> <john(dot)archie(dot)mckown(at)gmail(dot)com> wrote:
> >
> > SELECT avg(b.countcountry)::int as "CountryCount", b.country, a.city,
> > count(a.city) as "CityCount"
> > FROM t AS a
> > INNER JOIN
> > (SELECT COUNT(country) AS countcountry, country FROM t GROUP BY country)
> AS
> > b
> > ON a.country = b.country
> > GROUP BY b.country, a.city
> > ORDER BY 1 DESC,4 DESC;
> >
>
> I am curious that nobody pointed out that the above might work but is
> really poor code. Given that, I wonder what the people here think of
> the following code. It seems "better" to me, even if it is more wordy.
>
> WITH CountryCount AS (
> SELECT COUNT(country) as "countryCount",
> country
> FROM t
> GROUP BY country
> ),
> CityCount AS (
> SELECT COUNT(city) as "cityCount",
> city,
> country
> FROM t
> GROUP BY country, city
> )
> SELECT b."countryCount",
> b.country,
> a.city,
> a."cityCount"
> FROM CityCount as a
> INNER JOIN
> CountryCount AS b
> ON a.country = b.country
> ORDER BY b.countcountry DESC,
> a.city DESC
>
>
> --
> There is nothing more pleasant than traveling and meeting new people!
> Genghis Khan
>
> Maranatha! <><
> John McKown
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>