how can write a query that displays the 3 most populous cities in each state?-CodePudding

I hope you're doing well. as a newbie I would like to solve this, but at the end, the result is wrong. we have 2 tables. the City, it look like below ["1" "New York City" "8336817" "NY", "2" "Los Angeles" "3979576" "CA", "3" "Chicago" "2693976" "IL",...].

and the State that looks like below. ["AK" "Alaska" "731545", "AL" "Alabama" "4903185", "AR" "Arkansas" "3017825",...].

with this query, I fetched the most 3 populated cities in California.

select top 3 st, cityname, population from City where st='CA' ORDER BY population desc;

this query results: ["CA" "Los Angeles" "3979576", "CA" "San Diego" "1423851", "CA" "San Jose" "1021795"]

then with this query, i try to fetch the most 3 states in the country.

select top 3 statename, population from State ORDER by population DESC;

the result is : ["California" "39512223", "Texas" "28995881", "Florida" "21477737"]

now, i try to solve my question, for each of the most populated provinces; the 3 most populated cities did not show. please help me to solve this!

select top 3 st, cityname, population from City
where st in (select top 3 st from State ORDER by population DESC) ORDER by population DESC;

CodePudding user response：

We can use the DENSE_RANK() analytic function here:

WITH cte AS (
    SELECT *, DENSE_RANK() OVER (PARTITION BY statename ORDER BY population DESC) rnk
    FROM yourTable
    WHERE statename IN ('California', 'Texas', 'Florida')
)

SELECT statename, cityname, population
FROM cte
WHERE rnk <= 3
ORDER BY statename, rnk;