Let's say I have the following table:

I want the table to show me all customers from the city with the most orders in a given country, so for example for the chart above I wouldn't get Steven's value because London has more orders than Manchester within U.K

I would also like the table to show me how many customers are from that given city

and so I would like to get...

thx for the help

CodePudding user response：

Three steps are needed:

1. count the number of rows per country and city
2. get the maximum count for each country
3. only keep rows the count of which matches their country's maximum count

The query:

select
  customer, country, city, cnt
from
(
  select
   customer, country, city, cnt,
   max(cnt) over (partition by country) as max_cnt
  from
  (
    select
      customer, country, city,
      count(*) over (partition by country, city) as cnt
    from mytable
  ) counted
) counted_with_max
where cnt = max_cnt;