Find Max value of a count 1

[twifosp]

I have some data that looks like this:

region_code city zip 
AAA     Houston 77076
AAA     Houston 77024
AAB     Houston 77024

The region_code is entered manually by a multitude of different people. It should always be the same thing for each city, but some times it gets messed up.

I am trying to write a query that will let me find the cities that have more than one region code per city, and then update them based on which region code appears more than the other region code.

So far I have:

select city, count(*) as n
  from (
    select distinct pid, city, region_code
    from  customer_dim
   ) a
  group by city
  having count(*) > 1
  order by count(*) desc

Which finds all the cities that have more than one region code assigned to them per city.

The below code is my attempt to find the max value for each city and return its region code, but of course it doesn't work because I am not doing the sub queries right. Can someone help?

select  region_code, city
from  (
     select  region_code, city, count(*) as n
  from  customer_dim
  where city = 'HOUSTON'
  group by region_code, city) a
join  (
  select max(n) as maxn
  from  (
  select  region_code, city, count(*) as n
  from  customer_dim
  group by region_code, city) aa
  ) b
on a.city = b.city
and b.maxn = a.n

 

[SantaMufasa]

Twifosp,

There are many ways one can solve your need. Here is one that comes to mind:

select * from customer_dim;

PID CITY    REGION_CODE
--- ------- -----------
AAA Houston 77076
AAA Houston 77024
AAB Houston 77024
AAC Dallas  75201

select pid,city,cnt
  from (select pid,city,count(*)cnt
          from customer_dim
          where city = 'Houston'
          group by pid,city
         having count(*) > 1) x
 where cnt =
       (select max(cnt)
          from (select count(*)cnt
                  from customer_dim
                 where city = 'Houston'
                 group by pid,city
                )
        );

PID CITY           CNT
--- ------- ----------
AAA Houston          2

Let us know if this solution works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[twifosp]

SantaMufasa,

Thank you for the response. It helps me out, but I should have been more clear about my data.

I need to look at a data set that has many cities.

Essentially I want to return something like this:

City    dstnct_rgn_codes max_rgn_code  max_rgn_code_count
houston    2                   aaa           47
new york   1                   abc           34
boston     4                   efg           12

This way I can use the data set to see which cities are assigned to more than one region code and make a database wide update.

 

[Dagon]

I assume that, by maximum region_code, what you really mean is the most commonly occurring one. Something of this sort is probably what you need.

select *
from
(select city, region_code, rec_num, rank() over (partition by city order by rec_num desc) as ranking 
from 
(select city,
        region_code,
        count(*) as rec_num
   from customer_dim
 group by city, region_code))
where ranking=1
order by city, region_code

 

[twifosp]

Dagon -- Thank you. I did not know about the partition and ranking functions in Oracle. Good to learn something new.