SQL Query to get most populated ZIP code for a CITY in a STATE from my existing table with data

I have a table 'People' with these columns : id, fname, lname, addr, city, state, zip in Oracle 9.x

I want to get Most Populated Zip Code Per City Per State

I wrote this query:

Select City, State, Zip, count(*)
From People
Group By City, State
Order By count(*)

But it gives me MULTIPLE rows for a City For a State, Like (made up):-

City  --  State -- Zip   -- Count(*)
City0 --  ST0   -- 32111 -- 50
City1 --  ST1   -- 11223 -- 100
City1 --  ST1   -- 11225 -- 90
City1 --  ST1   -- 11226 -- 50
City2 --  ST1   -- 11255 -- 70
City3 --  ST2   -- 55443 -- 60

I tried HAVING clause like: Having Count() = max(Count()) BUT got an error message : NESTED having clause too deep or somethig (Oracle 9.x)

I want only TOP row :- City1 -- ST1 -- 11223 -- 100 for all City1 - ST1 rows, Other rows where there is only one row for per city per state that will remain same. WANTED OUTPUT:-

City  --  State -- Zip   -- Count(*)
City0 --  ST0   -- 32111 -- 50
**City1 --  ST1   -- 11223 -- 100**
City2 --  ST1   -- 11255 -- 70
City3 --  ST2   -- 55443 -- 60

How can I achieve this? Thanks for viewing.

==========

ANSWER FOUND FROM JIM HUDSON's REPLY By Modifying the provided query a little I got it right. FINAL CODE:-

select city, state, zip, counter from  (
select city, state, zip, count(*) as counter f开发者_如何学JAVArom people group by city, state, zip
)  
where counter = (
    select max(count2) from (
        select city as city1, state as state1, zip as zip1, count(*) as count2 from people group by city, state, zip   )
    where city=city1 and state=state1
);

select * from (
  select city, state, zip,
    rank() over (partition by city, state order by cnt desc) rank
  from (
    Select City, State, Zip, count(*) cnt
    From People
    Group By City, State, Zip
  )
)
where rank = 1

Think of your first query as a view. It gives you the counts for city, state, zip. Then use that as the starting point. It would then be easy to get the population in the biggest zip.

For example,

select city, state, max(counter) from
(select city, state, zip, count(*) as counter from people group by city, state, zip)
group by city, state;

Of course, that's not quite what you want since you want to know which zip that is. So the old way would be something like

select city, state, zip, counter from
(select city, state, zip, count(*) as counter from people group by city, state, zip)
where counter = (select max(count2) from (select city, state, zip, count(*) as count2 from people group by city, state, zip));

There are other ways to do the second step using analytic functions. I'll let someone else work on those. But the critical step is creating the inline view and using that as the basis for your further analyses.