Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks John.


On Thu, Aug 28, 2014 at 2:35 PM, John McKown <john.archie.mckown@xxxxxxxxx> wrote:
On Mon, Aug 18, 2014 at 10:52 AM, John McKown
<john.archie.mckown@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux