CODEWITHSUNDEEP
sqlrawsql
sbuck89
sbuck89

Reputation: 133

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.

Upvotes: 0

Views: 256

Answers (3)

A_kat
A_kat

Reputation: 1537

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

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 2006

Try This:

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

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;

Upvotes: 1

Related Questions