How to make a raw SQL query that selects the city with the highest population associated with an area code (there are duplicate area codes)

I have a table that has the following columns: areaCode zipcode city state population

There are multiple rows with the same area codes that are associated with different cities/zip codes. I need to select the city with the highest population that corresponds to the area code.

EX:

area_codes / zip / city / state / population

858 94111   San Francisco   CA  3905
858 94121   San Francisco   CA  34786
914 10010   New York    NY  22785
914 10012   New York    NY  17738

I want to be able to only select the city San Francisco (with area code of 858) that has the population of 34786 and the row New York (with area code of 914) that has the population of 22785, since they have the highest populations. After selecting these ones I need to create a new table and place them in a new table.

Solution

Try This:

Create table MaxPopulation as (select area_codes,city,max(population) from table group by area_codes,city);