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)

Question

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.

Which database are you using? – Tim Biegeleisen Feb 21 '20 at 05:53 — Tim Biegeleisen, Feb 21 '20 at 05:53
What if you have ties? – Gordon Linoff Feb 21 '20 at 12:43 — Gordon Linoff, Feb 21 '20 at 12:43

score 1 · Answer 1 · answered Feb 21 '20 at 05:55

1

A general solution uses ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY area_codes ORDER BY population DESC) rn
    FROM yourTable
)

SELECT area_codes, zip, city, state, population
FROM cte
WHERE rn = 1;

answered Feb 21 '20 at 05:55

Tim Biegeleisen

502,043
27
286
360

score 1 · Accepted Answer · answered Feb 21 '20 at 05:56

1

Try This:

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

answered Feb 21 '20 at 05:56

Naveen Kumar

1,988
1
7
11

score 1 · Answer 3 · answered Feb 21 '20 at 07:58

INSERT INTO cte_new
SELECT 
   area_codes,
   zip,
   city,
   state,
   MAX(population) population
FROM cte
GROUP BY
   area_codes,
   zip,
   city,
   state

Generally I would prefer a grouping function than a window function (parition) since it offers a much better performance. I have skipped the table declaration that you would require before this code

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)

3 Answers3