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

i have table 'people' these columns : id, fname, lname, addr, city, state, zip in oracle 9.x

i want populated zip code per city per state

i wrote query:

select city, state, zip, count(*) people group city, state order count(*) 

but gives me multiple rows city state, (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()) got error message : nested having clause deep or somethig (oracle 9.x)

i want top row :- city1 -- st1 -- 11223 -- 100 city1 - st1 rows, other rows there 1 row per city per state 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 achieve this? viewing.

==========

answer found jim hudson's reply modifying provided query little got right. final code:-

select city, state, zip, counter  ( select city, state, zip, count(*) counter people group city, state, zip )   counter = (     select max(count2) (         select city city1, state state1, zip zip1, count(*) count2 people group city, state, zip   )     city=city1 , state=state1 ); 

think of first query view. gives counts city, state, zip. use starting point. easy population in biggest zip.

for example,

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

of course, that's not quite want since want know zip is. old way like

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

there other ways second step using analytic functions. i'll let else work on those. critical step creating inline view , using basis further analyses.